Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: April 2010

A DMV A Day – Day 30

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…

Read more

9 comments, 1,764 reads

Posted in Glenn Berry's SQL Server Performance on 30 April 2010

A DMV a Day – Day 29

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

5 comments, 1,349 reads

Posted in Glenn Berry's SQL Server Performance on 29 April 2010

A DMV a Day – Day 28

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

3 comments, 574 reads

Posted in Glenn Berry's SQL Server Performance on 28 April 2010

SQL Server 2008 and 2008R2 Diagnostic Information Queries

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

7 comments, 1,099 reads

Posted in Glenn Berry's SQL Server Performance on 27 April 2010

A DMV a Day – Day 27

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…

Read more

3 comments, 3,950 reads

Posted in Glenn Berry's SQL Server Performance on 27 April 2010

SQL Server 2005 Diagnostic Information Queries (April 2010)

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

1 comments, 518 reads

Posted in Glenn Berry's SQL Server Performance on 26 April 2010

A DMV a Day – Day 26

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

4 comments, 612 reads

Posted in Glenn Berry's SQL Server Performance on 26 April 2010

A DMV a Day – Day 25

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

6 comments, 827 reads

Posted in Glenn Berry's SQL Server Performance on 25 April 2010

A DMV a Day – Day 24

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

5 comments, 420 reads

Posted in Glenn Berry's SQL Server Performance on 24 April 2010

A DMV a Day – Day 23

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…

Read more

3 comments, 1,028 reads

Posted in Glenn Berry's SQL Server Performance on 23 April 2010

A DMV a Day – Day 22

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…

Read more

5 comments, 1,054 reads

Posted in Glenn Berry's SQL Server Performance on 22 April 2010

Big SQL Server Announcement This Morning at 7:30AM PDT

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…

Read more

1 comments, 291 reads

Posted in Glenn Berry's SQL Server Performance on 21 April 2010

A DMV a Day – Day 21

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

5 comments, 1,441 reads

Posted in Glenn Berry's SQL Server Performance on 21 April 2010

Staying Current with SQL Server Builds

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

0 comments, 502 reads

Posted in Glenn Berry's SQL Server Performance on 20 April 2010

A DMV a Day – Day 20

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…

Read more

3 comments, 485 reads

Posted in Glenn Berry's SQL Server Performance on 20 April 2010

SQL Server 2005 SP3 Cumulative Update 9 (Build 4294)

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

4 comments, 498 reads

Posted in Glenn Berry's SQL Server Performance on 19 April 2010

A DMV a Day – Day 19

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

2 comments, 1,214 reads

Posted in Glenn Berry's SQL Server Performance on 19 April 2010

Spring Planting 2010

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

0 comments, 366 reads

Posted in Glenn Berry's SQL Server Performance on 19 April 2010

Goggle PowerMeter Update

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

0 comments, 257 reads

Posted in Glenn Berry's SQL Server Performance on 19 April 2010

A DMV a Day – Day 18

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…

Read more

3 comments, 621 reads

Posted in Glenn Berry's SQL Server Performance on 18 April 2010

Older posts