February 1, 2012 at 8:37 pm
OK, so I've been noticing a cpu spike on our development server that happens every 10 seconds or so. It's like a heart beat monitor but it's about every 10 seconds and it kicks the cpu up to about 45-50% cpu usage. I've been pestering our server admins about the issue for almost 2 months now for help with resolving the issue, but we don't really have DBA's in house and since this is application developments server, they haven't really been able to solve the problem.
So tonight, I log in remotely from home and there is an issue that causes me to have to reboot my pc to solve the issue. When I get reconnected to the machine I log back into the sql server and lo and behold the cpu spikes are gone. But they instantly reappear as soon as I open SSMS and connect to the server. Ugh. So it's been me all along, but I have no clue how or why I'm causing it. If I wasn't already bald I'd be pulling my hair out right now.
Can anyone give me an idea of what might be causing this. It seems to really slow down the server at times because it's always running when I'm connected. (I'm the team lead in charge of reporting so it's not really an option for me to not connect to my development server.)
My version of SSMS installed is as follows:
Microsoft SQL Server Management Studio10.50.2500.0
Microsoft Analysis Services Client Tools10.50.2500.0
Microsoft Data Access Components (MDAC)6.1.7601.17514
Microsoft MSXML3.0 4.0 5.0 6.0
Microsoft Internet Explorer9.0.8112.16421
Microsoft .NET Framework2.0.50727.5448
Operating System6.1.7601
Addins:
SQL Prompt 5.2.2.1 Professional Edition
SQL Search 1.1.4.3 Free Edition
PC is a Dell Precision T3500 with Intel Xeon dual core CPU (W3505 @2.53 ghz)
Windows 7 Ultimate 64 bit
12 gb of RAM
server I'm connecting to is on a virtual server (unfortunately). SQL Server @@version returns:
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) Sep 22 2011 00:28:06 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM)
February 2, 2012 at 12:20 am
- did you disable connection pooling ?
- Could it be your tools are using an "auto refresh feature" every 10 seconds ?
e.g. like used with "job activity monitor" ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2012 at 5:14 am
:w00t:
Try these important links below
FIX: The CPU usage of the server reaches 100% when many DML activities occur in SQL Server 2005
February 2, 2012 at 7:45 am
Since the server is SQL Server 2008 and my version of SSMS is SSRS 2008 R2, the SQL Server 2005 fixes do not apply.
February 2, 2012 at 7:52 am
Can you disable connection pooling just for your instance of the ssms connections? It doesn't seem to be happening when anyone else is connecting to the box.
I've pored over the options available in SQL Prompt and can't find anything in there that would cause this. We have 10 other people who are using SQL Prompt here as well, so I don't think it's the cause. SQL Search only seems to reindex when you change your database or server so I don't think it's the cause either, but I'd be willing to investigate it if someone thinks otherwise.
February 2, 2012 at 8:18 am
I'd run a profiler trace. Look for any batch requests coming from your computer. You probably have some tool running that is causing this.
The probability of survival is inversely proportional to the angle of arrival.
February 2, 2012 at 8:32 am
We have run traces, but I'm a self taught developer with virtually no admin training or experience, so I don't really know what I'm looking for. Do you have a suggestion for what I should be looking for?
I just ran another one and this is the only thing I see that looks like it might cause this issue: This was caused by me expanding down to the database and tables and I work with a lot in object explorer.
exec sp_executesql N'
DECLARE @is_policy_automation_enabled bit
SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)
FROM msdb.dbo.syspolicy_configuration
WHERE name = ''Enabled'')
SELECT
dtb.name AS [Database_Name],
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(dtb.name,'''''''') + '']'' AS [Database_Urn],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server''+ ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [Database_PolicyHealthState],
dtb.recovery_model AS [Database_RecoveryModel],
suser_sname(dtb.owner_sid) AS [Database_Owner],
case
when dtb.collation_name is null then 0x200
else 0
end |
case
when 1 = dtb.is_in_standby then 0x40
else 0
end |
case dtb.state
when 1 then 0x2
when 2 then 0x8
when 3 then 0x4
when 4 then 0x10
when 5 then 0x100
when 6 then 0x20
else 1
end
AS [Database_Status],
dtb.compatibility_level AS [Database_CompatibilityLevel],
ISNULL(dmi.mirroring_role,0) AS [Database_MirroringRole],
ISNULL(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM
master.sys.databases AS dtb
LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
WHERE
(CAST(case when dtb.name in (''master'',''model'',''msdb'',''tempdb'') then 1 else dtb.is_distributor end AS bit)=@_msparam_0 and CAST(isnull(dtb.source_database_id, 0) AS bit)=@_msparam_1)
ORDER BY
[Database_Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_m
February 6, 2012 at 7:52 am
Any one have any ideas on what I should do? I'd hate to have to rebuild my machine over this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply