Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance issue with a single database Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 2:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
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.
Post #1535643
Posted Tuesday, January 28, 2014 3:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1535647
Posted Tuesday, January 28, 2014 5:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Yes, i was able to view the graph and cost from the studio, but i could not make head or tail out of it!
Post #1535677
Posted Tuesday, January 28, 2014 6:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 3,374, Visits: 7,303
The issues you're facing are all covered in this book: Troubleshooting SQL Server: A Guide for the Accidental DBA.
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1535680
Posted Tuesday, January 28, 2014 6:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
thanks
Post #1535687
Posted Tuesday, January 28, 2014 7:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 2,826, Visits: 8,464
You can also look to see if processes are getting blocked.


Post #1535692
Posted Wednesday, January 29, 2014 12:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1535742
Posted Wednesday, February 26, 2014 3:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
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
Post #1545288
Posted Wednesday, February 26, 2014 3:06 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1545289
Posted Wednesday, February 26, 2014 6:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
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!?
Post #1545657
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse