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

SQL SERVER – DMV – sys.dm_exec_query_optimizer_info – Statistics of Optimizer

January 7, 2011

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

Incredibly, SQL Server has so much information to share with us. Every single day, I am amazed with this SQL Server technology. Sometimes I find several interesting information by just querying few of the DMV. And when I present this info in front of my client during performance tuning consultancy, they are surprised…

SQL SERVER – Finding Last Backup Time for All Database

November 4, 2010

Pinal Dave

SQL Tips and Tricks

SQL Backup, SQL DMV, SQL Scripts, SQL Server

Here is the quick script I use find last backup time for all the database in my server instance.

SQL SERVER – Find Total Number of Transactions on Interval

October 4, 2010

Pinal Dave

SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL Server, SQL System Table

In one of my recent Performance Tuning assignment I was asked how do someone know how many transactions are happening on server during certain interval. I had handy script for the same. Following script displays transactions happened on server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.

SQL SERVER – Find Queries using Parallelism from Cached Plan

July 24, 2010

Pinal Dave

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

Best Practices, SQL DMV, SQL Scripts, SQL Server

I recently came across wonderful blog post of Feodor Georgiev. He is one fine developer and like to dwell in the subject of performance tuning and query optimizations. He is one real genius and original blogger. Recently I came across his wonderful script, which I was in fact writing myself…

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

July 4, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Index, SQL Scripts

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.