Archives: April 2010
The DMV for Day 30 is sys.dm_os_buffer_descriptors, which is described by BOL as:
Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according…
The DMV for Day 29 is sys.dm_exec_connections, which is described by BOL as:
Returns information about the connections established to this instance of SQL Server and the details of each connection.
This DMV can be used to help identify who and what is connecting to an instance of SQL… Read more
The DMV for Day 28 is sys.dm_io_pending_io_requests, which is described by BOL as:
Returns a row for each pending I/O request in SQL Server.
That is a pretty straightforward, if terse description. This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER… Read more
Since SQL Server 2008 R2 has gone RTM, and will be available on MSDN Subscribers on May 3, I thought it was time to post an updated version of the Diagnostic Information queries. This has updated build lists for SQL Server 2008, SQL Azure, and SQL Server 2008 R2,… Read more
The DMV for Day 27 is sys.dm_tran_locks, which is described by BOL as:
Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the…
Since SQL Server 2008 R2 has gone RTM, and will soon be available for purchase, I am going to be spending even less time talking about SQL Server 2005 in the future. If you are on SQL Server 2005, you should try to make sure that you are on at… Read more
Well, we are on the final week of the DMV a Day series for the month of April. I will be posting a recap of the series on May 3, that will have links and a summary for each post in the series. I really appreciate all of the comments… Read more
The DMV for Day 25 is sys.dm_os_memory_cache_counters, which is described by BOL as:
Returns a snapshot of the health of a cache. sys.dm_os_memory_cache_counters provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.
The column single_pages_kb is the amount of… Read more
The DMV for Day 24 is sys.dm_exec_requests, which is described by BOL as:
Returns information about each request that is executing within SQL Server.
This DMV is useful for getting a quick snapshot of currently executing requests on your instance of SQL Server. This DMV works with SQL Server… Read more
The DMV for Day 23 is sys.dm_os_process_memory, which is described by BOL as:
Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations. However, memory allocations might be performed in the SQL Server address…
The DMV for Day 22 is sys.dm_exec_query_memory_grants, which is described by BOL as:
Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this…
Microsoft’s Ted Kummert and Tom Casey are holding a public conference call this morning at 7:30AM Pacific time, where:
Microsoft Corp. is holding a teleconference with Senior Vice President Ted Kummert and General Manager of Business Intelligence Tom Casey, who will make an announcement related to Microsoft SQL Server. A…
The DMV for Day 21 is sys.dm_os_ring_buffers, which is helpfully NOT described by BOL as:
The following SQL Server Operating System–related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Ooh, that sounds scary! Well despite that warning and lack of documentation… Read more
There was some conversation on Twitter about how to get notification when new Cumulative Updates are released for SQL Server 2005 and SQL Server 2008. Aaron Bertrand (blog | twitter) had a good post that discusses some of the ways of finding out when a new Cumulative Update… Read more
The DMV for Day 20 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 text, the amount of memory taken…
Microsoft has released SQL Server 2005 SP3 Cumulative Update 9, which is Build 4294. I count 13 fixes in this build. There is no corresponding cumulative update for SQL Server 2005 SP2, since SP2 is no longer a supported service pack for SQL Server 2005.
Even though Microsoft CSS… Read more
The DMV for Day 19 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
Spring fever is running hot here in Parker, Colorado, after a long winter. Last year, I put in a Square Foot Garden, and I added a few small containers with tomatoes. This year, I have added a second Square Foot Garden, and bought two Earth Boxes. I have also… Read more
As I have mentioned before, I have a TED 5000 electric power monitor installed in my house that monitors and records my electric power usage on a second by second basis. The TED 5000 can also upload data to Google PowerMeter, so you can access your current and historical… Read more
The DMV for Day 18 is sys.dm_clr_tasks, which is described by BOL as:
Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code…