February 9, 2011 at 9:48 am
Hello, I have a development server where the SQL Server Service a few times the last months has been using 100% processor power for no reasonable reason.
When I use sp_who2 to see what is going on, I can see a spid that is in EXECUTE mode started by me from my development machine/Management Studio. But I have no window in Management Studio open with that spid! It is consuming loads of CPU but no Disk I/O.
If I kill it it will be in Killed/Rollback Mode for a long time, still consuming lots of CPU. Only way to get rid of it is to restart SQL Server Service.
If use dbcc inputbuffer(spid) to see what the spid is doing I get some strange info (see below).
Someone who knows what's going on?
(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000),@_msparam_6 nvarchar(4000),@_msparam_7 nvarchar(4000)) create table #tmp_sp_catalogs (is_catalog_support bit null,server_name nvarchar(128) null, product_name nvarchar(128) null,provider_name nvarchar(128) null,catalog_name nvarchar(128) null, description nvarchar(4000) null) declare @ServerName sysname declare @ProductName sysname declare @ProviderName sysname declare crs cursor local fast_forward for ( SELECT srv.name AS [Name], srv.product AS [ProductName], srv.provider AS [ProviderName] FROM sys.servers AS srv WHERE (srv.server_id != 0)and(srv.name=@_msparam_0) ) open crs fetch crs into @ServerName,@ProductName,@ProviderName while @@fetch_status >= 0 begin DECLARE @IsCatalogSupport bit create table #tmp_catalog_exist_test (id int null,description sysname null,flags varchar null) insert into #tmp_catalog_exist_test(id,description,flags) EXEC master.dbo.xp_prop_oledb_provider @ProviderName select @IsCatalogSupport = count(*) from #tmp_catalog_exist_test where id = 233 if (UPPER(@ProviderName) like 'SQLNCLI%' ) begin set @IsCatalogSupport = 1 end if (@IsCatalogSupport = 0) begin insert into #tmp_sp_catalogs (catalog_name,is_catalog_support) values ('default',0) end else begin BEGIN TRY insert into #tmp_sp_catalogs (catalog_name,description) EXEC master.dbo.sp_catalogs @server_name = @ServerName update #tmp_sp_catalogs set is_catalog_support = 1 END TRY BEGIN CATCH insert into #tmp_sp_catalogs (catalog_name,is_catalog_support) values ('default',0) END CATCH end update #tmp_sp_catalogs set server_name = @ServerName update #tmp_sp_catalogs set product_name = @ProductName update #tmp_sp_catalogs set provider_name = @ProviderName fetch crs into @ServerName,@ProductName,@ProviderName end close crs deallocate crs create table #tmp_sp_tables_ex (is_catalog_error bit null,server_name nvarchar(128) null,server_catalog_name nvarchar(128) null,TABLE_CAT sysname null, TABLE_SCHEM sysname null,TABLE_NAME sysname null,TABLE_TYPE varchar(32) null,REMARKS varchar(254) null) create table #tmp_sp_tables_ex_all (TABLE_CAT sysname null, TABLE_SCHEM sysname null,TABLE_NAME sysname null,TABLE_TYPE varchar(32) null,REMARKS varchar(254) null) declare @TableServerName sysname declare @TableCatalogName sysname declare @IsCatalogSupportExist bit declare TableServerCrs cursor local fast_forward for ( SELECT tsc.server_name AS [ServerName], tsc.catalog_name AS [Name], tsc.is_catalog_support AS [IsCatalogSupport] FROM sys.servers AS srv INNER JOIN #tmp_sp_catalogs AS tsc ON tsc.server_name=srv.name WHERE (tsc.catalog_name=@_msparam_1 and tsc.is_catalog_support=@_msparam_2)and((srv.server_id != 0)and(srv.name=@_msparam_3)) ) open TableServerCrs fetch TableServerCrs into @TableServerName,@TableCatalogName,@IsCatalogSupportExist while @@fetch_status >= 0 begin IF (@IsCatalogSupportExist=0) BEGIN insert into #tmp_sp_tables_ex_all (TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS) EXEC master.dbo.sp_tables_ex @table_server = @TableServerName ,@table_name = NULL ,@table_schema = NULL ,@table_catalog = NULL ,@table_type = NULL insert into #tmp_sp_tables_ex (TABLE_CAT,TABLE_SCHEM,TABLE_NAME,T
February 9, 2011 at 11:12 am
Check for scheduled jobs - this may be some sort of a monitoring tool.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 10, 2011 at 1:15 am
I forgot to write my setup:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
It is a virtual server running on Vmware ESX
April 29, 2011 at 10:23 am
We get the same problem in our envrinment on occasion and I've found a way to repro it in our environment consistently. It's quite aggravating to be forced to restart for situation like this. And almost maddening on production box.
Our current configuration is nearly identical to Clark Bones':
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
We've seen this occur on both VM and physical servers.
Here's one way we are able to repro:
Create a Linked Server on the local SQL Server and link it to another SQL Server and map all users to a specific remote SQL login. Once created, simply use the Object Explorer in SSMS (I'm using SSMS 2008R2, but we've also seen this with SSMS 2008) from your workstation and attempt to view the catalog objects on the remote via the Linked Server. This produces a xp_prop_oledb_provider call against master and a last wait type of PREEMPTIVE_OS_GETPROCADDRESS and I've never seen it finish. Just hangs.
In our case all the servers in question are 2008 Enterprise SP1 with no CUs and the remote SQL account has access to only a specific DB on the remote SQL box.
To me, it feels like a bug, so I'm going to leverage some of our Premier Support hours with MS Support if I can't find a solution. I'll post back what I find out for future searches.
-Patrick
April 29, 2011 at 10:27 am
Forgot to add that the SPID on the host server where the problem is occurring is orphaned, meaning, I have physically closed SSMS and the SPID just stays in a killed/rollback state, but in a runnable status with 1 open transaction. Super weird......
April 30, 2011 at 1:36 pm
Cursor.....Cursor.... Cursor...... Kill that Cursor.... Has been the Rule.
Cursor is the Culprit. The query you captured is a Cursor. Cursors will cause 100% CPU often times. So try to avoid Cursors and use Set based queries instead.
Most probably its a Agent Job running under your account that's causing issue.
Thank You,
Best Regards,
SQLBuddy
May 1, 2011 at 9:36 am
sqlbuddy123 (4/30/2011)
Cursor.....Cursor.... Cursor...... Kill that Cursor.... Has been the Rule.Cursor is the Culprit. The query you captured is a Cursor. Cursors will cause 100% CPU often times. So try to avoid Cursors and use Set based queries instead.
Most probably its a Agent Job running under your account that's causing issue.
Thank You,
Best Regards,
SQLBuddy
Look at the code. See the variables with names like "@_msparam_0"? The OP didn't write such code. Microsoft did. 😉 It's tough to avoid cursors written by the boys in Redmond. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2012 at 5:39 am
Hi there - I am experiencing exactly the same problem with the same code being executed on a SQL 2008 RTM. Does anyone know if this is a bug?
January 10, 2012 at 6:08 am
Kwisatz78 (1/10/2012)
Hi there - I am experiencing exactly the same problem with the same code being executed on a SQL 2008 RTM. Does anyone know if this is a bug?
Start a new thread, these kind of problems need more specific debugging.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply