CPU fluctuation in SQL

Question

Post reply

CPU fluctuation in SQL

dr_csharp

SSCommitted

Points: 1765
More actions
September 8, 2010 at 3:03 am

#226665

I have a database with millions of records contain NEWS, at this time I have about 800 online users who read data from database.Server CPU usage is fluctuating between 30 and 90 and all is allotted to "sqlservr.exe".I ran "Sql Profiler" at that time and checked the requests but i saw somethings strange, please see the picture :
as you can see in picture all request was done shortly except highlighted row that had "329 in CPU, 202 in Duration and 19204 Read"
I ran its query manually and i get the result rapidly !
I want you to tell me why this fluctuation happen?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Does that particular query always run with that many reads? (There's only one entry for it in the profiler trace)

It was running rapidly in the trace (200ms), so the fact that it ran fast manually says nothing.

Does it do the same number of reads when run manually?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

Does that particular query always run with that many reads? (There's only one entry for it in the profiler trace)
Does it do the same number of reads when run manually?

I have attached a new pic that i ran it manually ( its same as prior)

It was running rapidly in the trace (200ms), so the fact that it ran fast manually says nothing.

sorry, I made a mistake.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, so it does always have a high reads. Looks like it needs optimising.

I'm not sure I fully understand what you're asking.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 4

I'm not sure I fully understand what you're asking.

I think the fluctuating CPU usage by sqlservr.exe, may cause for this heavy reads. how can i fix these?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Could be. You need to optimise this and other poorly-performing queries.

If you have no idea where to start, take a look at this 2-part series

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

If you'd like our help in optimising this, please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 6

If you'd like our help in optimising this, please post table definitions, index definitions and execution plan

my query is :

select top 1 [name],address,nextmedia1,[description] from media where look=1 and name ='100942885226.jpg' order by tdmedia

I have attached the table definition and execution plan in following file :

please download from here:

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

No indexes whatsoever on the table?

If not, I'd venture a guess that the complete lack of indexes is the reason this query is not optimal.

I suggest that if the lack of indexes extends to other tables, you really need to do a performance tuning exercise on this DB.

For this query, try this index.

CREATE NONCLUSTERED INDEX idx_Media_NameLook ON [dbo].[media] ([name],[look])

p.s. You have a clustered index on a GUID. That's about the worst possible place for a clustered index.

Is this a vendor app or something developed in-house? If it's at all possible, move the clustered index onto the identity column you have and make the primary key nonclustered.

This clustered index is going to be causing massive fragmentation and it really won't be helping your performance issues.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass