I had a performance problem recently with SQL Server, and I went through the standard performance checklist, however it didn't solve the problem permanently. Sometimes it would perform well, but most times it was performing poorly. I knew the next step was to mess with the execution plan. This is something I really don't like. Read more »
I have been working on a performance problem recently, so you might see several blog entries with information that help me. Hopefully they will help you.
Getting the SQL Server execution plan from a production can be difficult, since you are not running the code within Enterprise Manager. You can still get the execution plan of any running statement and display the graphical representation in Enterprise Manager. Read more »
The following query lists the tables and the space they use. This query is much faster (sub-second) than a standard SELECT COUNT(*) query since it uses the dynamic management views in SQL Server rather than scanning your data.
SELECT sum ( used_page_count ) * 8 AS SizeKB, sum(row_count) AS [RowCount], object_name ( object_id ) AS TableName FROM sys.dm_db_partition_stats WHERE index_id=0 OR index_id=1 GROUP BY object_id ORDER BY sum ( used_page_count ) DESC;