Archives: April 2010
The DMV for Day 17 is sys.dm_db_index_usage_stats, which is described by BOL as:
Returns counts of different types of index operations and the time each type of operation was last performed.
This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission. Read more
We have two DMVs for Day 16. The first one is sys.dm_exec_cached_plans, which is described by BOL as:
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query…
The DMV for Day 15 is sys.dm_os_performance_counters, which is described by BOL as:
Returns a row per performance counter maintained by the server. For information about each performance counter, see Using SQL Server Objects.
This DMV is very useful, but can be very frustrating to work with. Depending… Read more
Microsoft’s Bob Ward has a new post up on the CSS SQL Server Engineers blog about a change in their lifecycle support policy for unsupported service packs of products that are still in mainstream support. Previously, Product Support could not help you in this situation unless you had purchased a… Read more
The DMV for Day 14 is sys.dm_os_wait_stats, which is described by BOL as:
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
There are several very useful… Read more
Microsoft’s Scott Guthrie had a good post up yesterday that highlights some of the new features in Visual Studio 2010. After some initial issues downloading the iso file early yesterday, I was able to get it downloaded and installed as you see below.
I am looking forward to converting… Read more
The DMV for Day 13 is sys.dm_io_virtual_file_stats, which is described by BOL as:
Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
This DMV has two parameters, which are database_id and file_id. You can specify NULL for either parameter, in which case,… Read more
Microsoft has made the RTM bits for the various versions and components of Visual Studio 2010 available on MSDN Subscribers as of 9AM Pacific time this morning. Mary Jo Foley has some details here. Unfortunately, it looks like their download infrastructure is getting slammed, since I, (and quite a… Read more
The DMV for Day 12 is sys.dm_db_partition_stats, which is described by BOL as:
Returns page and row-count information for every partition in the current database.
You can find some interesting information from sys.dm_db_partition_stats, including what is in the query shown today. This particular DMV works with SQL Server 2005,… Read more
It is day 11 of 30, of a DMV a Day for the month of April 2010. The DMV for Day 10 is sys.dm_db_index_usage_stats, which is described by BOL as:
Returns counts of different types of index operations and the time each type of operation was last performed. Every…
Just in case you’ve just joined me, I decided on April 1 to post a different DMV query every day for the month of April. People seem to be enjoying the series, so that gives me added motivation to keep going. Fellow SQL MVP Paul Randal (blog | twitter Read more
The DMV for Day 9 is sys.dm_os_schedulers, which is described by BOL as:
Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.
You can discover several… Read more
We have two related DMVs for Day 8. The first one is sys.dm_fts_active_catalogs, which is described by BOL as:
Returns information on the full-text catalogs that have some population activity in progress on the server.
The second one is sys.dm_fts_index_population which BOL describes as:
Returns information about the full-text…
I have three different DMVs for Day 7. The first one is sys.dm_db_missing_index_group_stats, which is described by BOL as:
Returns summary information about groups of missing indexes, excluding spatial indexes. Information returned by sys.dm_db_missing_index_group_stats is updated by every query execution, not by every query compilation or recompilation. Usage statistics…
This screenshot goes in the category of “geek porn”… It shows an IA64 system with 256 logical processors and 512GB of RAM, running at 72% CPU utilization. It is obviously running SQL Server 2008 R2 on Windows Server 2008 R2, since earlier versions of Windows Server and SQL Server are… Read more
Well, we finally got the ok to start talking about the second 24 Hours of PASS event, called 24 Hours of PASS: Celebrating SQL Server 2008 R2, which starts at 12:00 GMT on May 19, 2010. In case you are not familiar with the event, is is 24 hours… Read more
The DMV for Day 6 is sys.dm_db_index_usage_stats, which is described by BOL as:
Returns counts of different types of index operations and the time each type of operation was last performed. Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as…
Microsoft’s Dan Reger has announced that Microsoft will not be supporting the Itanium architecture after Windows Server 2008 R2, SQL Server 2008 R2, and Visual Studio 2010. Mainstream support for Itanium on Windows Server 2008 and Windows Server 2008 R2 will end on July 9, 2013.
I can see why… Read more
The DMV for Day 5 is sys.dm_db_mirroring_auto_page_repair, which is described by BOL as:
Returns a row for every automatic page-repair attempt on any mirrored database on the server instance. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100…
The DMV for Day 4 is sys.dm_os_sys_memory, which is described by BOL as:
Returns memory information from the operating system. SQL Server is bounded by, and responds to, external memory conditions at the operating system level and the physical limits of the underlying hardware. Determining the overall system state…