The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for December, 2012

A scenario on Query Tuning–Included Columns are a good thing!

Posted by Sudarshan Narasimhan on December 20, 2012


Well, this post has been due for quite some time. I had opened up a survey/opinion poll on https://thesqldude.com/2012/07/10/user-opinion-poll-what-topics-would-you-like-to-see-posted-on-thesqldude-weblog/ sometime back and as expected most of you out there want to see more on performance tuning and query optimization.

image

  Figure 1: Opinion Poll Results

Without much ado and by popular demand here goes a simple scenario on query tuning.

Tuning

One of my engineers came to me with the following scenario…

Problem Statement

We have this Update query that is taking much longer to run compared to before. No schema changes have been made though its quite possible data changes have been happening. The update query goes like this,

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard 
where mb_type = 210

Table Schema

I was easily able to reproduce this issue on my instance, here is how my table setup looks.

/****** Object:  Table [dbo].[msgboard]    Script Date: 12/20/2012 01:52:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[msgboard](
    [mb_id] [int] NOT NULL,
    [mb_type] [int] NOT NULL,
    [mb_status] [int] NOT NULL,
    [mb_changed_by] [varchar](50) NULL,
    [mb_changed_dt]  AS (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [pk_msgboard] ON [dbo].[msgboard] 
(
    [mb_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_msgboard_type] ON [dbo].[msgboard] 
(
    [mb_type] ASC,
    [mb_status] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

I populated this table with some sample data of 1000 rows using the following script.

declare @counter int
set @counter=0
declare @mbid int
set @mbid=1
declare @mbtype int
set @mbtype=200
declare @mbstatus int
set @mbstatus=10
while(@counter<1000)
begin
INSERT INTO [dbo].[msgboard]
           ([mb_id]
           ,[mb_type]
           ,[mb_status]
           ,[mb_changed_by])
     VALUES
           (@mbid,@mbtype,@mbstatus,'THESQLDUDE')
set @mbid+=1
set @counter+=1
end
GO
INSERT INTO [test].[dbo].[msgboard]
           ([mb_id]
           ,[mb_type]
           ,[mb_status]
           ,[mb_changed_by])
     VALUES
           (1001,210,10,'MANWITHNONAME')

So, I have 2 indexes on this table:-

1) A unique Clustered Index pk_msgboard on the mb_id column

2) A composite non-clustered index IX_msgboard_type on the columns mb_type and mb_status (in this order).

Query Execution Details

Here is what I observed when I ran the following update statement.

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard 
where mb_type = 210

To actual execution plan looks like this,

image

Total Cost: 0.03124485

As you can see, this query is using the Clustered Index pk_msgboard and its performing a Scan. At this point, two questions should pop into your head

1. Why is the query using the clustered index?

2. Why is it performing a Scan operation?

Answer to question #1

Let’s look at the update query carefully. The query is updating the columns mb_status and mb_changed_by, and there is a filter on column mb_type. You might think, well, I have an index IX_msgboard_type on the mb_type column, why is SQL Server not using this non-clustered index?

To prove I was smarter than SQL Server optimizer, I forced it to use the non-clustered index by adding an index hint. The query looks like this,

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard WITH(INDEX(IX_msgboard_type))
where mb_type = 210

image

Total Cost: 0.03651106

  • Notice that the Cost for the query with index hint is higher than the one without! If the table had more rows, we would have seen a bigger difference in cost Smile.
  • Additionally, a Table Spool operator is now part of the execution plan. The table spool operator caches the row to be updated (In tempdb) and then feeds it to the Clustered Index update operator.
  • This proves that SQL Server optimizer was actually choosing the cheapest and best plan it could come up with.

The reason SQL Server picked the clustered index is because the query is updating the columns mb_status =10 and mb_changed_by. This column mb_status is not the leading column in the NC index IX_msgboard_type, but the 2nd column. Why does this matter? It does because, the statistics for this NC index would be built on the leading column which is mb_type. Here is how the histogram looks.

dbcc show_statistics ('dbo.msgboard','IX_msgboard_type')

image

To avoid a bookmark lookup (Key Lookup operator from SQL 2005 onwards), SQL Server decided to use the clustered index since it covers all the columns in the table. If SQL Server were to use the NC index, it would be do a lookup or cache the results (ahem, Table Spool operator!) and then find the matching row in the clustered index to actually update the 2 columns. This is an additional cost, and this is why SQL decided not to go with this plan.

Coming to the 2nd question of why a Scan? It might surprise some of you, but a Seek does not imply good performance always. My favourite answer – “It depends”. In this case, the table only had 1001 rows and of which only 1 row (mb_id=1001) qualified for the update. When using the CI Scan, SQL Server was applying the filter on mb_type=210 and that returned 1 row back. A Clustered Index Seek in this case would not make any positive difference in query execution time.

OBJECT:([test].[dbo].[msgboard].[pk_msgboard]), WHERE:([test].[dbo].[msgboard].[mb_type]=(210)) ORDERED

To test this, I added the FORCESEEK table hint to this update query and as expected the query did not run faster.

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard WITH (FORCESEEK) where mb_type = 210 

In fact it has the same cost as the query with index hint –> 0.03651106
 

Solution

Instead of trying to second guess the query optimizer and trying to tweak the execution plan it is producing, this issue needs a different approach –> back to basics. Based on the query design, if the column being updated mb_status was “covered” then it would help. In this case that was also also take care of, since IX_msgboard_type also includes the column mb_status, though as a non-key column. This is an important thing and this was why the “included columns” feature was introduced.

So, I modified the index IX_msgboard_type to remove the column mb_status. Instead, I added the column back as an included column

DROP INDEX [IX_msgboard_type] ON [dbo].[msgboard] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_msgboard_type] ON [dbo].[msgboard] 
(
    [mb_type] ASC
)
INCLUDE ( [mb_status]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The biggest advantage of having an included column in a non-clustered index is that, the query optimizer can locate all the column values within the index. The base table or clustered index data is not accessed resulting in fewer disk I/O operations and hence faster query execution. Also, the index size is lesser since non-key columns are only added to the leaf level. Many advantages indeed.

Here is how the update query plan looked after added mb_status as an included column.

image

My oh my, this plan looks so simple, clean and yeah faster. The actual cost is now 0.0132843, which is a big improvement over the original plan of 0.03651106

So we have achieved our original goals of

a) Getting SQL Server to use the Non-clustered index IX_msgboard_type, instead of the clustered index.

b) Getting an Seek operation instead of a Scan.

c) Query cost is cheaper.


So, hope everyone understood how beneficial included columns are on non-clustered indexes. For more information on guidelines and restriction with included columns refer this MSDN article.

That’s all for now folks. Keep tuned to this site for more.

-TheSQLDude (Sudarshan)

Posted in Performance, Query Optimization | Tagged: , , , , , , , , , , | 6 Comments »

T-SQL Script to find the size of every index in the database

Posted by Sudarshan Narasimhan on December 15, 2012


I wrote this query to find the size of all indexes in the database for one of my customers. Thought I’d share this script with everyone. Cheers.

CREATE PROCEDURE uspIndexSizeinDB
AS 
-- Author: Sudarshan (TheSQLDude.com)
-- Data Created: Dec 15, 2012
-- Description: Calculate the SQL Server index size for all indexes in the current database
  BEGIN 
    DECLARE  @IndexSize BIGINT, 
             @IndexID   INT,
             @IndexName nvarchar(200),
             @IndexType nvarchar(50),
             @ObjectID  INT
     
    SET @IndexSize = 0
    
    create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50))
    
    DECLARE curIndex CURSOR  FOR 
    SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc 
    FROM sys.indexes sysind 
    join sys.sysobjects sysobj
    on sysind.object_id = sysobj.id
    where sysind.type>0 and sysobj.type not in ('S','IT')
    order by sysobj.id asc
   
    OPEN curIndex 
     
    FETCH NEXT FROM curIndex 
    INTO @ObjectID, @IndexID, @IndexName, @IndexType
     
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) 
        FROM   sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') 
         
        insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType)
        SELECT TableName = OBJECT_NAME(@ObjectID), 
               IndexID = @IndexID, 
               IndexName = @IndexName,
               [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType
         
        FETCH NEXT FROM curIndex 
        INTO @ObjectID, @IndexID, @IndexName, @IndexType
      END 
    CLOSE curIndex 
    DEALLOCATE curIndex 
    
    select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)]  from #tmpresults order by [IndexSize(MB)] desc
  END 
GO

Posted in DB Maintenance, T-SQL | Tagged: , , , , , , | 2 Comments »

Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session

Posted by Sudarshan Narasimhan on December 14, 2012


I often use a multitude of T-SQL queries and scripts when I am troubleshooting memory issues and even for monitoring SQL Server Memory usage. I am posting these scripts here in the spirit of sharing, as I’m sure there are other variations of these same scripts out there already. For this purpose of this blog post, these scripts are only useful in troubleshooting out-of-memory (OOM) and other issues in the Buffer Pool. This blog post does not cover Virtual Address/Virtual Memory or memory tracking outside of the Buffer Pool. That will be covered in a later post.

In case you have other scripts for memory monitoring, please leave behind a comment

— Query to find the Buffer Pool usage per each Database
— Each of these pages are present in the Buffer Cache, meaning they are IN_RAM pages.

DECLARE @total_buffer BIGINT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE ‘%Buffer Manager’
AND counter_name = ‘Total Pages’
/AND counter_name = ‘Database pages’ — Uncomment this line & comment the above line, if you’re SQL Server version is 2012 or above/

;WITH BufCount AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG()
FROM sys.dm_os_buffer_descriptors
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[Database_Name] = CASE [database_id] WHEN 32767
THEN ‘MSSQL System Resource DB’
ELSE DB_NAME([database_id]) END,
[Database_ID],
db_buffer_pages as [Buffer Count (8KB Pages)],
[Buffer Size (MB)] = db_buffer_pages / 128,
[Buffer Size (%)] = CONVERT(DECIMAL(10,2), db_buffer_pages 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC;

Output

image

-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25 
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 COUNT_BIG(*) AS Buffered_Page_Count ,
 COUNT_BIG(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC

Output

image

-- Query to show current memory requests, grants and execution plan for each active session
-- This shows memory granted & requested for currently active sessions on the instance level
-- This can be used in a script to capture information over a period of time.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Output

image

Note: When you click on the query_plan, it opens up the XML Showplan in SSMS.

-- Query to search plan cache for queries with memory grants completed

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Output

image

-- Queries that have requested memory or waiting for memory to be granted
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

Note: This query can be used when there are active sessions waiting on memory to be granted and they are waiting. These sessions will have a wait_type of RESOURCE_SEMAPHORE. You can calculate the wait time for memory grant, by subtracting request_time and grant_time

-- SQL Server 2005/2008/R2 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;
-- SQL Server 2012 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Output

image
That’s all for now folks!

  • TheSQLDude (Sudarshan)

 

Posted in Memory, Performance, T-SQL | Tagged: , , , , , , , , , | 13 Comments »

Service Pack or Update for SQL Server 2008/R2/2012 fails without generating the setup log files!

Posted by Sudarshan Narasimhan on December 12, 2012


I was recently working on an interesting setup issue with one of our Premier customers. The scenario was this – they were trying to patch their SQL Server 2008 R2 instance with SP1 (KB 2528583) and it was failing, but the log files were not getting generated fully. In fact, the path C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ did not even have a folder with the latest timestamp. Interestingly, the log files were getting created on the D: drive location D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\

From D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ folder we could only find Summary.txt and Detail.txt

Summary.txt

Overall summary:
  Final result:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Exit code (Decimal):           1513770790
  Exit message:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Start time:                    2012-11-18 00:54:46
  End time:                      2012-11-18 01:21:23
  Requested action:              Patch

Requested action:              Patch
Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER\SQLSysClrTypes_Cpu64_1.log
Exception help link:           http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.2500.0

Note the highlighted link and that log file were not present at all!

Detail.txt

Every feature that was trying get patched logs this error –> MSI Error 1622. 1622 stands for ERROR_INSTALL_LOG_FAILURE, which means the setup program is not able to log setup messages to the log file and hence failed.

Reference: http://msdn.microsoft.com/en-us/library/windows/desktop/aa368542(v=vs.85).aspx

2012-11-18 00:56:46 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi"
2012-11-18 00:56:46 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 00:56:46 Slp: Watson Bucket 1

2012-11-18 01:03:00 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
2012-11-18 01:03:00 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 01:03:00 Slp: Watson Bucket 5

I search for the keyword “at microsoft” to find exception stack and I see this error was raised at the end

2012-11-18 01:21:19 Slp: Result error code: 1622
2012-11-18 01:21:19 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2012-11-18 01:21:19 Slp: Sco: Attempting to get registry value DW0201
2012-11-18 01:21:21 Slp: Received request to add the following file to Watson reporting: C:\Users\EGP00\AppData\Local\Temp\2\tmpC720.tmp
2012-11-18 01:21:21 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2012-11-18 01:21:21 Slp: Inner exceptions are being indented
2012-11-18 01:21:21 Slp:
2012-11-18 01:21:21 Slp: Exception type: System.IO.DirectoryNotFoundException
2012-11-18 01:21:21 Slp:     Message:
2012-11-18 01:21:21 Slp:         Could not find a part of the path ‘C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER’.
2012-11-18 01:21:21 Slp:     Stack:
2012-11-18 01:21:21 Slp:         at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
2012-11-18 01:21:21 Slp:         at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at System.IO.DirectoryInfo.GetFiles(String searchPattern, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonMsiBucket.ExecutePreCabTasks()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonService.ReportFailures()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Start()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
2012-11-18 01:21:21 Slp: Watson Bucket 17

The above message is strange since the path clearly exists and some log files are getting created there, but no the one with the failure. From a admin perspective, while this error is being logged, if you look at the setup screen you will see it frozen at this stage (MsiTimingAction),

image

 

To make progress on this, I decided to troubleshoot the log file generation, and why the log files are created on the D: drive.

On a Windows system, the Program Files and Program Files (x86) paths are hard-coded and are set as part of the environment for each program. To open the current program files location, one can just go to Run and type in %programfiles% and hit enter. When I did this on my customer’s machine, it opened up the D:\Program Files path. Now, we’re getting somewhere.

These paths are also stored in the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion, as shown below.

image

On this machine it was pointing to the D: drive and hence the log files were going there. But some of the log files, were expecting this to always be the C:\ location and hence failing. This explains the log file location mystery.

The solution was fairly simple from here on, since the location of the ProgramFiles cannot be configured and changing it is not supported as per http://support.microsoft.com/kb/933700

I changed the location back to the default value by editing the registry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion]

ProgramFilesDir         –> C:\Program Files
ProgramFilesDir (x86) –> C:\Program Files (x86)

You can also make this change for a specific user profile by adding a User Environment Variable called “ProgramFiles” and setting its value to the path of the program files dir.

image

After changing the registry key value, I rebooted for good measure (though I think a logoff and logon should suffice). Now, when I go to Run and do %programfiles% it takes me to the C: drive as expected.

Re-ran the SP1 for SQL Server and it completed fine without issues and the log files were also getting created in the default path

Just thought I’d share this issue since its an example of how system misconfiguration or issues outside of SQL can have an impact of setup and patching.

 

Posted in Setup | Tagged: , , , , , , | 1 Comment »

SQL Server 2012 Configuration Manager WMI Error – Remote Procedure call failed [0x800706be]

Posted by Sudarshan Narasimhan on December 5, 2012


I recently installed SQL 2008 R2 alongside my existing SQL Server 2012 instance. I wanted to change the service account for the SQL Agent service, so I opened SQL Server Configuration Manager (SSCM). To my surprise, I got this error. Bear in mind, this is a freshly installed OS and SQL combination.

image

Error Text: The remote procedure call failed. [0x800706be]

Considering this to a WMI error, I recompiled the MOF file –> mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

I even un-registered and re-registered the sql provider DLL –> regsvr32 "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll"

But the issue was not resolved. This was working before and all I did was to install another instance of SQL 2008 R2 which went fine without any issues.

Next, I used wbemtest utility to connect to the SQL namespace and query the list of SQL Service. I was able to connect to the namespace root\microsoft\sqlserver\computermanagement10, but the query “select * from SqlService” returned this error

image

Basically, this was the same error as before. Interestingly, I could connect to the SQL 2012 namespace root\microsoft\sqlserver\computermanagement11, and the query to list the SQL Services worked as well. I checked the application event log and found this “Application Error” Event ID 1000. This tells me that the wmiprvse.exe (WMI host process) is crashing due to an issue with svrenumapi100.dll. This is the DLL that SQL Server uses to perform discovery of services.

Log Name:      Application
Source:        Application Error
Date:          12/5/2012 10:49:28 PM
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      XYZ.domain.com
Description:
Faulting application name: wmiprvse.exe, version: 6.2.9200.16384, time stamp: 0x5010ad15
Faulting module name: svrenumapi100.dll, version: 2009.100.1600.1, time stamp: 0x4bb681be
Exception code: 0xc0000005
Fault offset: 0x00000000000514e6
Faulting process id: 0x1458
Faulting application start time: 0x01cdd30cad78774b
Faulting application path: C:\windows\system32\wbem\wmiprvse.exe
Faulting module path: C:\Program Files\Microsoft SQL Server\100\Shared\svrenumapi100.dll

Log Name:      Application
Source:        Windows Error Reporting
Date:          12/5/2012 10:44:58 PM
Event ID:      1001
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      XYZ.domain.com
Description:
Fault bucket -1203304158, type 1
Event Name: APPCRASH
Response: Not available
Cab Id: 0

Problem signature:
P1: wmiprvse.exe
P2: 6.2.9200.16384
P3: 5010af6d
P4: svrenumapi100.dll
P5: 2009.100.1600.1
P6: 4bb682e9
P7: c0000005
P8: 0003a0bf
P9:
P10:

Attached files:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18A9.tmp.appcompat.txt
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18C9.tmp.WERInternalMetadata.xml
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18CA.tmp.hdmp
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18FA.tmp.dmp

These files may be available here:
C:\ProgramData\Microsoft\Windows\WER\ReportArchive\AppCrash_wmiprvse.exe_caa0d63711bd20d668bdb425a1333d9b5156bed4_17011eb4

Solution

If you have SQL Server 2012 running side-by-side on the same machine with SQL Server 2008/2008 R2, then you need to have SQL Server 2008 SP3 or later (or) SQL Server 2008 R2 SP1 or later. This is required to obtain the edition information when performing a SQL Server discovery across the enterprise. I applied SP2 on my SQL 2008 R2 instance and that fixed the issue with configuration manager. If you run into this issue, this piece of information might help you.

-TheSQLDude

Posted in Tools | Tagged: , , , , , , | 20 Comments »