Bill Galashan, DBA of bet365 sent over the following query that lists the execution plan of the 10 most frequently executed queries.
He writes:
We got into this due to different query plans coming from a VB or a web app than what was seen when running the same query from Management Studio. Eventually tracked this down to a difference in the set options predominatley whether Arithabort was on or off.
Read more to see his query. (more...)
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. (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.
Update: Here is a query to get the execution plan of the most frequently used queries.
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. (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;