Index Related Stuff
Finding when Stats were Updated |
|
I wanted to know when the last time the Statistics were updated. I found this query. Pretty useful. It uses the STATS_DATE function.
Use sys.dm_db_stats_properties for 2012 and up.
--Use this for 2008 instances
SELECT OBJECT_NAME(id) AS TABLEName ,
name AS StatsName,
STATS_DATE(id, indid) AS LastStatsUpdate ,
rowmodctr AS RowModCounter
FROM sys.sysindexes
WHERE STATS_DATE(id, indid) <= DATEADD(DAY, -1, GETDATE())
AND rowmodctr > 0
AND id IN ( SELECT object_id
FROM sys.tables )
ORDER BY LastStatsUPdate DESC
--This will give you more accurate results for 2012 and up including the sample rate as well the percent of change in the table
SELECT sp.stats_id ,
OBJECT_NAME(s.object_id) AS tablename ,
s.name ,
sp.last_updated,
sp.rows ,
sp.rows_sampled ,
CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
CASE WHEN sp.modification_counter = 0 THEN 0
ELSE CAST(sp.modification_counter AS NUMERIC)
/ CAST(sp.rows AS NUMERIC) * 100
END AS Percent_changed ,
CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
AS sp
WHERE sp.last_updated <= DATEADD(DAY, -1, GETDATE())
AND sp.rows > 10000
Use sys.dm_db_stats_properties for 2012 and up.
--Use this for 2008 instances
SELECT OBJECT_NAME(id) AS TABLEName ,
name AS StatsName,
STATS_DATE(id, indid) AS LastStatsUpdate ,
rowmodctr AS RowModCounter
FROM sys.sysindexes
WHERE STATS_DATE(id, indid) <= DATEADD(DAY, -1, GETDATE())
AND rowmodctr > 0
AND id IN ( SELECT object_id
FROM sys.tables )
ORDER BY LastStatsUPdate DESC
--This will give you more accurate results for 2012 and up including the sample rate as well the percent of change in the table
SELECT sp.stats_id ,
OBJECT_NAME(s.object_id) AS tablename ,
s.name ,
sp.last_updated,
sp.rows ,
sp.rows_sampled ,
CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
CASE WHEN sp.modification_counter = 0 THEN 0
ELSE CAST(sp.modification_counter AS NUMERIC)
/ CAST(sp.rows AS NUMERIC) * 100
END AS Percent_changed ,
CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
AS sp
WHERE sp.last_updated <= DATEADD(DAY, -1, GETDATE())
AND sp.rows > 10000
Update Stats job for large databases |
|
I have a database that is almost 12 TBs and grows at 340 GBs a month. I need something a little more dynamic than Olla Hallengren script to update stats. That is our standard for all other databases. This script I wrote update stats based up number of rows in the table and percentage of change in the tables. This will only work on 2012 and up because it uses the new update stats function dm_db_stats_properties. Here are the comments I included in the top of the script. I haven't turned it into a stored procedure yet. That is my next step. We also turned on Trace Flag 2371 to help with statistics for large tables.
UPDATE STATS JOB THAT UPDATES STATS BASED UPON ROW COUNT PER TABLE.
1 - 100K
100K- 500K
500K- 1MIL
1MIL- 10MIL
10MIL- 100MIL
100MIL-250MIL
250MIl-500MIL
500MIL-1BIL
1BIL+
FOR EACH ROWCOUNT DESIGNATION ASSIGN A SCAN PERCENTAGE OF THAT TABLE WHICH ARE SET TO DEFAULT SETTINGS IN THE VARIABLES BELOW. CHANGE AS NEEDED
ALSO PERCENT OF CHANGE IN THE TABLE IS CALCULATED. BASED UPON THAT CHANGE THEN UPDATE THE STATS. THE THRESHOLD IS SET TO A DEFAULT SETTING BUT CAN BE TWEAKED AS WELL.
YOU CAN ALSO HAVE TO POPULATE @NUMBER_OF_MIN TO DETERMINE HOW LONG THE JOB WILL RUN.
ALL UPDATED STATS ARE LOGGED INTO MASTER..STATS_LOG AND ARE TRUNCATED AT EACH EXECUTION OF THE SP.
UPDATE STATS JOB THAT UPDATES STATS BASED UPON ROW COUNT PER TABLE.
1 - 100K
100K- 500K
500K- 1MIL
1MIL- 10MIL
10MIL- 100MIL
100MIL-250MIL
250MIl-500MIL
500MIL-1BIL
1BIL+
FOR EACH ROWCOUNT DESIGNATION ASSIGN A SCAN PERCENTAGE OF THAT TABLE WHICH ARE SET TO DEFAULT SETTINGS IN THE VARIABLES BELOW. CHANGE AS NEEDED
ALSO PERCENT OF CHANGE IN THE TABLE IS CALCULATED. BASED UPON THAT CHANGE THEN UPDATE THE STATS. THE THRESHOLD IS SET TO A DEFAULT SETTING BUT CAN BE TWEAKED AS WELL.
YOU CAN ALSO HAVE TO POPULATE @NUMBER_OF_MIN TO DETERMINE HOW LONG THE JOB WILL RUN.
ALL UPDATED STATS ARE LOGGED INTO MASTER..STATS_LOG AND ARE TRUNCATED AT EACH EXECUTION OF THE SP.
update_stats.sql | |
File Size: | 33 kb |
File Type: | sql |
Best ReIndex and ReOrg solution
If you are needing a ReIndexing solution, most folks point you to Ola Hallengren scripts but this is better.
Its much more configurable and really easy to setup, from the folks at MidnightDBA.
http://minionware.net/
Its much more configurable and really easy to setup, from the folks at MidnightDBA.
http://minionware.net/
Find Unused Indexes |
|
Need to find unused indexes. Here you go. Be careful if using this if SQL was just bounced as counters are reset for associated DMVs.
SELECT
DB_NAME(us.database_id) AS DB ,
t.name AS TABLE_name,
i.name Index_Name,
us.user_seeks ,
us.user_scans ,
us.user_lookups ,
us.user_updates ,
us.last_user_seek ,
us.last_user_scan ,
us.last_user_update
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.index_id = us.index_id
AND i.object_id = us.object_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE us.database_id = DB_ID()
AND i.is_primary_key = 0
AND us.last_user_seek IS NULL
AND us.last_user_scan IS NULL
AND us.last_user_lookup IS NULL;
SELECT
DB_NAME(us.database_id) AS DB ,
t.name AS TABLE_name,
i.name Index_Name,
us.user_seeks ,
us.user_scans ,
us.user_lookups ,
us.user_updates ,
us.last_user_seek ,
us.last_user_scan ,
us.last_user_update
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.index_id = us.index_id
AND i.object_id = us.object_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE us.database_id = DB_ID()
AND i.is_primary_key = 0
AND us.last_user_seek IS NULL
AND us.last_user_scan IS NULL
AND us.last_user_lookup IS NULL;