SQL DMV Archives - Page 22 of 24 - SQL Authority with Pinal Dave

SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats

July 4, 2010

In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index, and if there is any way I could demonstrate the different levels of…

SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

June 17, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts

This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query. Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I am usually…

SQL SERVER – List All the DMV and DMF on Server

May 15, 2010

Pinal Dave

SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL Server

“How many DMV and DVF are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Training.

SQL SERVER – Find Most Expensive Queries Using DMV

May 14, 2010

Pinal Dave

SQL Performance

SQL DMV, SQL Scripts, SQL Server

The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries in SQL Server Box.

SQL SERVER – Get Latest SQL Query for Sessions – DMV

May 5, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts

In recent SQL Training I was asked, how can one figure out what was the last SQL Statement executed in sessions. The query for this is very simple. It uses two DMVs and created following quick script for the same. SELECT session_id, TEXT FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST While…

SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit

April 20, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL System Table

During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double…

SQL SERVER – DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action

January 18, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts

I just received an email from one of the readers asking for help with error he encountered while attempting to run DMV. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. Fix/Solution/Workaround: The above error is usually generated when the user…