Friday, August 20, 2010

SQL Server 2008 Query Memory Usage

You may see Hash or Sort warnings in the default trace output and this is typically caused by query memory pressure.

If you would like to know how much memory a query is using, then the DMV sys.dm_exec_query_memory_grants may be able to help you.

An example for using the DMV is shown below:

DECLARE @mgcounter INT
SET @mgcounter = 1
WHILE @mgcounter <= 5 -- return data from dmv 5 times when there is data
  BEGIN
     IF (SELECT COUNT(*)
          FROM sys.dm_exec_query_memory_grants) > 0
        BEGIN
                 SELECT *
                 FROM sys.dm_exec_query_memory_grants mg
                             CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) -- shows query text
                 -- WAITFOR DELAY '00:00:01' -- add a delay if you see the exact same query in results
                 SET @mgcounter = @mgcounter + 1
        END
  END

Monday, August 16, 2010

SQL Server 2008 sys.dm_os_ring_buffers timestamp column

I have used the below code in SQL Server 2008 to reveal the sys.dm_os_ring_buffers timestamp column in a friendly format. 

SELECT Dateadd(ms,-1 * ((si.cpu_ticks / si.ms_ticks) - rb.TIMESTAMP),
Getdate()) AS time_stamp,
rb.*
FROM sys.dm_os_ring_buffers rb,
sys.dm_os_sys_info si
ORDER BY time_stamp DESC

Sunday, August 15, 2010

Friday, August 13, 2010

MAP 5.0

The free Microsoft Assessment and Planning Toolkit (MAP) version 5.0 has been available for a while and is extremely useful for performing a quick SQL Server site discovery and audit. The key improvements over MAP version 4 is the inclusion of database, server configuration and login permissions data.

http://technet.microsoft.com/en-us/library/bb977556.aspx

Guest Failover Clustering in a VMware virtualized environment

Interesting article about Microsoft support policy for Guest Failover Clustering in a VMware virtualized environment and why you may want to make use of this capability.

http://blogs.msdn.com/b/clustering/archive/2010/07/27/10042799.aspx

Microsoft SQL Server Migration Assistant for MySQL v1.0

Microsoft SQL Server Migration Assistant for MySQL v1.0 has been released which allows you to migrate from MySQL to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=69739c8c-ac82-41de-b9e6-8fa5ae2594d9

Premier Mission Critical

Microsoft offers an add-on to Premier Support called Premier Mission Critical which includes support for SQL Server:

http://www.microsoft.com/Presspass/press/2010/aug10/08-10MissionCriticalPR.mspx

Key extracts:

'After the initial consultation, customers receive a dedicated line for support, available 24 hours a day, seven days a week, with priority access to the most knowledgeable experts. If an incident occurs, Premier Mission Critical provides customers with the fastest problem resolution services available from Microsoft Corp., including a 30-minute response time and an engagement process that begins at an advanced support level.'

'Premier Mission Critical now provides support for the Microsoft .NET Framework, Microsoft BizTalk Server, Microsoft Exchange Server, Microsoft Office SharePoint Server, Microsoft Dynamics CRM, Microsoft System Center, Microsoft SQL Server and Windows Server. Support is currently available worldwide in English.'

'Pricing for Premier Mission Critical will be dependent on the level of engagement with Microsoft and the specific requirements of each customer. Customers wanting more information should contact their Microsoft account representatives.'