Clay Lenhart’s Blog

A blog on .Net and SQL Server

Posts Tagged dynamic management views

Execution Plan of Frequent Queries

May 29

Posted by Clay Lenhart in SQL Server Administration

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. Read the rest of this entry »

Tags: , , , , ,

No Comments

Cached Execution Plans in SQL Server

Apr 13

Posted by Clay Lenhart in SQL Server Administration

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. Read the rest of this entry »

Tags: , , , , , ,

No Comments

Table Size Query

Mar 8

Posted by Clay Lenhart in SQL Server Administration

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;

Tags: ,

No Comments