December 12, 2015 at 10:00 pm
Great thread, Grant.
For some very common tasks (who is doing what, table sizes in a database, which modules contain this text) I have some custom procedures I wrote and use very frequently.
Seems like I use this pair at least once a day:
SET STATISTICS IO, TIME ON;
SET STATISTICS IO, TIME OFF;
Lately, I've been helping others a lot with optimization. I find myself creating temp tables (to replace CTEs when needed) and implementing window functions (often with LAG/LEAD/FIRST_VALUE/LAST_VALUE).
I use this to check if some of the statistics need updating. Extra criteria is sometimes added in the WHERE clause if we only want to check one table or just a few.
SELECT
SchemaName = s.name
, ObjectName = o.name
, StatName = st.name
, IsIndex = IIF(i.index_id is null, 0, 1)
, StatsDate = STATS_DATE(st.object_id, st.stats_id)
FROM sys.stats st
INNER JOIN sys.objects o
ON o.object_id = st.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
LEFT JOIN sys.indexes i
ON i.object_id = st.object_id
AND i.index_id = st.stats_id
AND i.name = st.name
WHERE o.is_ms_shipped = 0
ORDER BY StatsDate, SchemaName, ObjectName, StatName
;
And then, if statistics do need updating, ...
UPDATE STATISTICS ...;
December 13, 2015 at 9:06 am
1)
SELECT
2)
CREATE VIEW
3)
CREATE PROCEDURE
4)
CTE
5)
Checking index fragmentation query (adding a lot of new indexes and rebuilding existing lately for a new rebuild.)
December 13, 2015 at 9:34 am
I probably should have followed up. I wrote up the article from this data. It's here at Simple-Talk.[/url]
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 13, 2015 at 9:21 pm
1. Inserting records in a table
2. Viewing all records from a table.
3. Viewing only selected records from a table
4. Deleting records from a table
5. Changing data in existing records in a table
Viewing 4 posts - 61 through 63 (of 63 total)
You must be logged in to reply to this topic. Login to reply