July 30, 2015 at 9:04 am
To monitor User_Scans User_Seeks User_Lookup User_Updates
See the code part below this 'message'
The purpose of this code is to monitor the number of scans/seeks/lookups/updates, during a specific period or during more specific periods.
For example somebody does a specific call and we want to know which tables are touched. Or why does a specific action take a long time.
In my surroundings this script is part of a stored procedure. Calling this script once every 10 seconds, gives some insight what is happening.
What I understand is this:
Seek: The number of times an index is accessed over a specific range, for example because of a where clause.
Scan: Full Index (or full table) scan.
Lookup: Lookup in the main table. For example when a seek results in a number of hits.
Updates: The number of updates (Mutations? Inserts? Updates? Deletes?) are done on an Index
Index can be an actual index, a clustered table or a Heap table.
Questions:
Any good descriptions on these counters ?
To me this code is sometimes usefull, is there something similar on the internet ?
(Which is probably better and has extra functionality ?).
I am posting this because I think this is maybe usefull to others.
And response on this message might be usefull for me and others.
This is not an actual problem. It is more a tool for finding problems.
Thanks for your time and attention,
Ben
The code does not alter a/the database.
But it creates a number of tables in the tempdb.
So in my opinion it is save to use.
(Actual runtime of the script is (depending) around half a second).
--------------------------------------------------------------------------------------------
-- ben brugman
-- 20150730
--
--------------------------------------------------------------------------------------------
-- This script is a part of a stored procedure which can monitor 'actions' on the sqlserver instance.
-- This script is supplied AS IS.
--
--
-- It collects the number of scans/seeks/loopup/updates.
-- A next run will give the number of scans etc. done on each table which has been touched.
-- Repeated runs of this script can give some insight in the number of scans,seeks etc. over a period.
--
--
-- If a table/index hasn't been touched since startup of a database.
-- The table/index does not get into the ##before script.
-- (next time it is included though, this is an ommision, sorry).
--------------------------------------------------------------------------------------------
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts')
select top 1 timestamp previous from ##Changed_counts
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts2') drop table ##Changed_counts2
Print '-- SHOW_USE_CHANGE '+convert(varchar(30),getdate(),126)
PRINT '--'
PRINT '-- Warning, this function is still SINGLE USER.'
-- SELECT '-- Warning, this function is still SINGLE USER.' [-- WARNING]
-- The tables are at te moment handled in a primitive way.
-- A single # table gave problems. So a double #table was choosen.
-- This will give trouble in concurrency situations.
--
--
-- ##before is the previous 'scan'
-- ##after is the current 'scan'
--
if object_id('tempdb..##before') is not null drop table ##before
if object_id('tempdb..##AFTER') is not null select * into ##before from ##AFTER
if object_id('tempdb..##AFTER') is not null drop table ##AFTER
-----------------------------------------------------------------------------------------------------------------
-- fill the current ##AFTER
SELECT
--database_id -- zou meegenomen kunnen worden. Change over meerdere databases.
--,
SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS
,max(p.rows) rows
,sum(user_seeks) user_seeks
,sum(user_scans) user_scans
,sum(user_lookups) user_lookups
,sum(user_updates) user_updates
,max(last_user_seek) last_user_seek
,max(last_user_scan) last_user_scan
,max(last_user_lookup) last_user_lookup
,i.type_desc
,i.name
,i.index_id
,'-----' [-----]
-- ,o.*
,'------' [------]
-- ,i.*
,'-------' [-------]
into ##AFTER
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE --i.type_desc = 'HEAP' and
SCHEMA_NAME(o.schema_id) <> 'sys'
and database_id = DB_ID()
group by SCHEMA_NAME(o.schema_id),object_name(i.object_id ),i.type_desc, i.name,i.index_id
ORDER BY rows desc
exec ('if object_id(''tempdb..##before'') is null select * into ##before from ##AFTER')
--
-- Isolate all rows in the dataset which have altered.
-- Keep them in ##Changed_counts
--
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
select GETDATE() timestamp, * into ##Changed_counts from (
select * from ##AFTER
except
select * from ##before ) as xxx
-- select * from ##changed_counts
--
-- Show all rows which are altered.
-- (Een mogelijkheid is deze changes Plus een timestamp op te slaan in nog en tijdelijke tabel).
-- (B.v. om gedurende een (korte) periode te tracen).
--
select
CONVERT(varchar(8), timestamp, 108) tijd
,a.
as table_name2
,A.NAME AS INDEX_NAME2
,A.INDEX_ID as INDEX_ID2
,a.user_seeks - b.user_seeks delta_seeks
,a.user_scans - b.user_scans delta_scans
,a.user_lookups - b.user_lookups delta_lookups
,a.user_updates - b.user_updates delta_updates
, a.*
into ##Changed_counts2
from ##Changed_counts A join ##before B on a.
= b.
AND A.INDEX_ID = B.INDEX_ID
--
-- Show the results
--
select * from ##Changed_counts2
--
-- show the cummulative results.
--
select
convert(varchar(30),GETDATE(),126)
, SUM(rows) rows
, SUM(delta_seeks) delta_seeks
, sum(delta_scans) delta_scans
, sum(delta_lookups) delta_lookups
, sum(delta_updates) delta_updates
from ##Changed_counts2
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
July 30, 2015 at 9:16 am
Lookups aren't related to seeks. Lookups will only be > 0 on a clustered index or heap. It's the number of key lookups/rid lookups done.
Updates are any modification. Insert, update, delete.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2015 at 9:32 am
GilaMonster (7/30/2015)
Lookups aren't related to seeks. Lookups will only be > 0 on a clustered index or heap. It's the number of key lookups/rid lookups done.Updates are any modification. Insert, update, delete.
Thanks for your reply.
Lookup: Lookup in the main table. For example when a seek results in a number of hits.
If an index (not being the main table) is used, first a seek is done on the index and then a lookup is done on the main table. If the seek does not find any candidates no lookup is done.
Or am I wrong with this? Please explain.
Ben
July 30, 2015 at 9:47 am
Nope.
A key/rid lookup is done to fetch columns which are not present in the index used. It's not related to a seek, you can get index scans with key lookups just as easily as seeks. They're only done if the query needs one or more columns which are not present in the index which the optimiser decided to use for the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2015 at 10:11 am
GilaMonster (7/30/2015)
Nope.A key/rid lookup is done to fetch columns which are not present in the index used. It's not related to a seek, you can get index scans with key lookups just as easily as seeks. They're only done if the query needs one or more columns which are not present in the index which the optimiser decided to use for the query.
Thanks, yes I had forgotten the covering indexes.
In our situations most indexes are not covering so did not realy take that into account.
Below some additional script
Thanks,
Ben
-- As I understood, Scans are full table (index or main).
-- So a full scan on a large tables (index) should be prevented.
--
-- Code below produces a histogram.
-- The number of rows of a table determines the class.
-- Each order of ten covers 2 classes.
-- Min and Max give the actual number of rows of the tables concerned.
--
-- Histogram of scans.
-- delta_scans The number of scans during the period.
-- Class For each power of 10, two classes are created.
-- Min/Max Actual min and max values present in de rows column in the table
--
-- The Histogram gives a 'fair' view of the scans.
--
select sum(delta_scans) delta_scans
,round(2*log10(1.0*rows),0)
as 'Class'
, MIN(rows) min
, MAX(rows) Max
from ##Changed_counts2 where rows > 0
group by
round(2*log10(1.0*rows),0)
order by
round(2*log10(1.0*rows),0)
Example:
Notice class 15 could be a 'problem'. Scans on large tables.
delta_scansClassminMax
772011
1617125
4382922617
308332055
48248461153
4855205528
361167951761
254718335021
2668591614934
691791054143
01067884163249
77011201615545967
90125639531721911
371320356085209387
014600871016550054
6151842073921892755
016112091250112091250
July 30, 2015 at 10:17 am
ben.brugman (7/30/2015)
-- As I understood, Scans are full table (index or main).-- So a full scan on a large tables (index) should be prevented.
Not necessarily. If a query needs a large portion of the table and the indexes aren't covering, a scan will be more efficient than seek + key lookups.
Also a scan doesn't mean the entire table. For eg, SELECT TOP(1) * FROM SomeTable will be a scan, but it'll return 1 row and only read enough of the table to get that one row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2015 at 7:34 am
Thanks again.
Did not realise that a select top 1 would count as a 'scan'.
I do use the TOP a lot, but not in a production environment. (At least not without an order or a where clause).
I do realise that the optimizer can switch to a Scan instead of using indexes.
(At the moment that is our problem. For one query an index hint did give a faster query, but more importend did far less impact on the cache and on the PLE).*)
My description of Scans, Seeks etc. is clearly not addequate. Is there anywhere a fairly good description of the scans seeks etc.
Current observation: The number of scans is not very important. The number of scans caused by full table scans on large tables is far more important.
(Offcourse this does depend on circumstances).
Ben
*)
Indexes and statistics are up to data.
July 31, 2015 at 11:18 am
A seek requires a predicate. A seek is a navigation of an index looking for a value or the start/end of a range. If there's no predicate (as in SELECT TOP 1 FROM Table), then you can't have a seek.
iirc there's definitions somewhere on my blog. Try the search box or google, I don't remember where it would be. Probably in one of my indexing posts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply