SQL Query performing really slow

Question

SQL Query performing really slow

MSBI Learner

Ten Centuries

Points: 1192
More actions
December 3, 2012 at 7:33 am

#285713

Experts –
We recently got a new server which at least 3 times better in terms of RAM, HD and rest of the configurations to our previous server. But when I ran a query in new server, it runs for very long however the same query runs pretty much faster in old server.
I could see the LastWaitType as PAGEIOLATCH_SH in new server for most of the queries.
Can someone please help me with this wait type?
P.S: There is nothing wrong with query to be tuned at all as the same query performs really well in old server so I suspect some configurations in new server which we are missing.
Thanks in advance !!

Viewing 6 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

you need to provide more details; the actual execution plan would tell us exactly what happened,a nd we could identify the items in the execution plan that is causing performance issues.

can you attach the .sqlplan here?

other than that, it'd immediately suspect that the new server is a higher version, and you restored the database, but did not rebuild the indexes or rebuild the statistics...

if that is the case, there's a known performance issue, because the engine in the higher version uses the statistics differently,and need to be rebuilt.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 2

Hello – what you have suspected is absolutely TRUE. Yes – new server is on SQL 2012 whereas the old one is on SQL 2008 R2.

Can you suggest or help with any blog on how to rebuild the statistics, I’ll ensure to rebuild the indexes though.

Thanks a ton

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Start with the statistics, absolutely. That fixes the majority of issues of this type. Running sp_updatestats will give you a basic statistics update. If you're not maintaining statistics in your database, you might be hitting issues that you're unaware of.

If that doesn't fix the problem, understand that the optimizer is changed in every version. You might be hitting one of the edge cases where a query that was problematic in the old version of SQL Server, but that ran well enough, now doesn't run well. To fix that, you have to go through standard query tuning techniques.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323546 More actions · Answer 4

this is what i would suggest first thing, since it's not a bad thing to update statistics:

DECLARE @Exec VARCHAR(MAX)

SELECT @Exec = ''

SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 5

Thank you so much Grant for suggestions and Lowerll for the script. I would try that and update you in case of any issues.

Thanks a ton !!