Performance issue with a single database

Question

Performance issue with a single database

mJai

Ten Centuries

Points: 1160
More actions
January 28, 2014 at 2:55 pm

#396179

Hi,
Im using the SQL 2008 R2. Iam required to perform two tests on one of our database as anything to do with it, seems very slow.
I have no control on the applications/query as it is a packaged vendor; however i do have some ability to initiate certain transactions from the application side that could be a possible culprit. I only have admin access at the database side. I know the name of the database in question that iam suspecting.
1) What tests can i perform on the sql side on this database so i can identify and confirm it.
2) How do i test for fragmentation on this database and its indexes?
3) What tools or scripts if available can help me to identify the cause?
Please guide. Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Can you generate execution plans? It would be very helpful to see the actual execution plan from a query that is slow.

For maintaining indexes you should check out this link. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mJai Ten Centuries Points: 1160 More actions · Answer 2

Yes, i was able to view the graph and cost from the studio, but i could not make head or tail out of it!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

The issues you're facing are all covered in this book: Troubleshooting SQL Server: A Guide for the Accidental DBA[/url].

Take a look and make it your faithful companion.

EDIT: And be sure to check the other books in the Books section from this site. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mJai Ten Centuries Points: 1160 More actions · Answer 4

mJai

Ten Centuries

Points: 1160

January 28, 2014 at 6:42 pm

#1684122

thanks

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

You can also look to see if processes are getting blocked.

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

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

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

mJai Ten Centuries Points: 1160 More actions · Answer 7

Hey,

Im back at this again. I rebuilt my index and that fixed it!

However , now after couple of days ( i should say ), the database is exhibiting the same problem. I know that if i rebuild the index again, that would fix it.

Also, upon checking for fragmentation i notice that some of the tables in this Database has the value as 100 for the column - avg_fragmentation_in_percent.

I rechecked after performing another rebuild but the value still shows 100 ? WHy is that so? I was hoping that it would show a more ideal value for the avg_fragmentation_in_percent.

Thanks,

Jai

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

Too small for rebuilding to matter?

It's unlikely that rebuilding the index fixed the problem. It's far more likely that the statistics update which the rebuild performs fixed your problem. Consider adding some manual statistics updates on the tables you identified.

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

mJai Ten Centuries Points: 1160 More actions · Answer 9

yes, I did think so! and then i went on to verify the views that show me the statistics last update.

I then realise that we also have a job that runs and updates the statistics almost every day. This did reflect in the STATS_DATE(A.OBJECT_ID,A.INDEX_ID) from the Indexes and Objects.

So thats what confuses me..that even though we do have the stats getting updated everyday, the stats (detailed sampling for fragmentation ) arent getting any better. Weird!?

mJai Ten Centuries Points: 1160 More actions · Answer 10

Im assuming and expecting a value of 0 to be most ideal

mJai Ten Centuries Points: 1160 More actions · Answer 11

GilaMonster,

You're right!

Its the stats update. That fixed it. However Im trying to understand how this works. Would you be able to give some idea? Thanks.

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

Google SQL Server statistics and do some reading. Far more than I can summarise here.

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