SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query performance


Query performance

Author
Message
raghu.nit
raghu.nit
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 253
Some of developers have made modifications to database structure like indexes and later observed that the performance of queries have degraded. As per the information of the developers all the changes have been reverted back but still the performance is bad.

We took the backup of the database before changes and restored on the same server. We could see the query performing well on the restored database. While observing the execution plans we see index scans ,whereas the same index structure exists on the all the tables involved.

Verified indices ,stats and have updated them as well still the issue is not resolved

Can you please suggest what other aspects to be checked. Thank you in advance for help
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222094 Visits: 42003
I would first ask the Developers how they knew they rolled everything back and how they knew they didn't drop something that they didn't add.

The other thing might be that things are "stuck". In other words, maybe you need to do a DBCC FREEPROCCACHE followed by a DBCC DROPCLEANBUFFERS to get all of the stored procedures to recompile. Bear in mind that that act alone will make things run slowly at first because all of the data and procs that are normally cached will need to recache.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
raghu.nit
raghu.nit
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 253
Thank you Jeff Moden for your reply.

I have compared the indexes on the database(which was restored by using a backup before the modifications).
The index structures are same on both the databases. When I observe the execution plan I could see Index scans which were due to the absence of seek predicates.

We are using a SQL 2012 server. Any tips would be helpful. Same structure on one database is using Index seeks and on the other Index scans.

Thanks,
Raghu
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24535 Visits: 13559
Like Jeff already suggested: it might be required to recompile the code since the cached query plans might still not reflect the new index.

Jeff suggested the DBCC FREEPROCCACHE followed by a DBCC DROPCLEANBUFFERS.

That's the very first time I disagree with him (and most probably I'll be proven wrong...) Blush

I'd rather go "step by step" and start with a single sproc that I know performs badly.
Recompile this sproc separately either by using the option WITH RECOMPILE or by running EXEC sp_recompile N'yourSchema.YourSproc' and verify this will actually help to improve performance.

Once you know that recompile is the solution you could either recompile the sprocs and queries step by step or run the DBCC FREEPROCCACHE followed by a DBCC DROPCLEANBUFFERS as suggested.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233031 Visits: 46361
Worth noting that a restore clears the plan cache for the restored database, so plans could not have persisted from before the restore.

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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222094 Visits: 42003
GilaMonster (12/2/2013)
Worth noting that a restore clears the plan cache for the restored database, so plans could not have persisted from before the restore.


Absolutely understood and agreed. I was talking about the original database where they "rolled back" the changes and were still having problems. Perhaps I misunderstood what was actually done.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33387 Visits: 8682
Compare the amount of data in the tables involved. Perhaps someone did some inserts or deletes that changed estimated row counts.

Verify that the queries are EXACTLY the same, and I do mean EXACT. If views or functions are involved, verify them down to the character as well.

Verify the actual schema of all tables involved. Someone could have changed a column from a varchar(20) to an Nvarchar(20).

Check data value distributions. Someone could have updated data which caused estimated rows to vary between the two database copies.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
peter-757102
peter-757102
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1779 Visits: 2559
This can also be a simple issue of out of date statistics.
SQL servers build in auto update statistics (if its on in your database) does not always do the job when you want or need it for better query plans.
Is there a nightly job updating them?

Finally isolate the few queries that (likely) cause slowdown.
You might find that due to growing data volumes some specific indexes or SQL statement alterations are in order.
Always keep in mind that a database that works with small volumes can have drastically different behavior whith multiple times the data volumes.
A larger volume brings brings existing inefficiencies to light and makes them suddenly measurable/noticable.

And then one more thing. Is the load of the system increased and do you use ad-hoc queries?
You should make sure that you use parameterized queries whenever possible and in some situations even make sure the optimizer knows to make a generic plan, instead of focusing all its smartness around the first parameter values it encounters and then stick to that (i am referring to option( optimize for unknown ) that is available since 2008).

Then there is yet one more possibility. many developers use the SQL Server management studio to alter databases, sometimes just to place a new column in an estatically pleasing position withing the table.

DON'T EVER!!

I tell you why...management studio contains bugs that when a table needs to be rebuild lets filters on indexes suddenly disappear!

Oh...and don't forget disk, file and log (number of virtual log files) fragmentation.
The defaults (on earlier versions at least) set auto growth in percentages and is very nonsensical, causing fragmentation.

As you can see, the list of things that can make a database go south with respect to performance are numerous, in this regard its not even clear i am helping here other then to relay just this fact.
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13788 Visits: 4077
Another ONe cent.

Has the restore done on server having same hardware configuration(if done on different server ) ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10586 Visits: 5157
Hi

It depends what indexes were changed. If it was a clustered index, then have in mind that REBUILD-ing it does not rebuild the associated nonclustered indexes automatically unless the ALL keyword is specified.
It's the same for re-creating a clustered index. If it was the case, than practically, you have the nonclustered indexes useless.

Regards,
Igor Micev

Igor Micev,
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search