| Author |
Topic |
|
dc_calif
Starting Member
5 Posts |
 Posted - 2012-06-12 : 19:19:29
|
| I have a SQL 2008 database that is relatively small, 80 gig that has some serious performance issues at the database level.I’m running SQL 2008 R2 had have almost 200 users but most of them are idle. The task manager is shows little going on with CPU rarely going over 50% and Page File at 2 or 3 gig.Its running on a Hyper V Cluster 2008 R2 for the host and the database is virtualized with 4 dedicated processors. The OS is Windows 2003 Enterprise R2.The Cluster is barely doing anything.If I do an large read, export or import of data it’s fairly slow to complete. Overall the performance of the database is rather sluggish.The database is only support a single application and database and nothing else, no reporting services or any other products, just the database. Even late at night with all the users off the performance is rather poor.All the defaults are set to default for the server properties with no limits to memory or processors.We do database re-index’s and optimization with no changes.One thing that I did notice is fragmentation. For example, I have a rather small table that maintains inventory which has 2,200 records that gets very few records added every day, maybe 1 or 2 but gets updated a lot. If I re-index the table within a mins the fragmentation level is over 50%. I have the fill factor on the index set to 80% and the option to pad the index.I’m at lose why the fragmentation is so high so quickly and trying to see if this might be the problem?Thanks |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-12 : 19:23:12
|
| I've seen weirdness with SQL Server on some virtual machines and great performance on others. Are your disks part of a SAN? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-12 : 19:28:41
|
| Ignore the small tables in regards to fragmentation. Set your fill factor to 100 and see if that fixes your performance problem. We had SEVERE performance degradation when using values under 100. We did extensive performance testing. There was only a teeny, tiny performance improvement for inserts with a lower fill factor, but HUGE performance degradation for reads.If that doesn't fix it, I'd suggest looking into missing indexes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-12 : 19:32:56
|
| Tara, are you certain the poor performance is caused by the fragmentation? |
|
|
|
dc_calif
Starting Member
5 Posts |
Posted - 2012-06-12 : 19:37:41
|
| The disks are part of a SAN and the SAN is only being used currently to support this enviorment.I have checked for missing indexes and there are none.I did have the fill factor at 100% prior to setting it to 80 when I started looking into this.Even my larger tables have a high fragmenation level. I have a table with 6 million rows and did a reindex with a fill factor of 80% and within an hour it was back to 23% fragmentaion. Is that normal?One other item that is a bit odd and might be a clue, I have 32 gig of memeory on the system and dedicated 20 gig to SQL yet when I look at performance monitor it never got over 2 gig. With all the users and all that was being asked its hard to beleive that it only needed 2 gig today. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-12 : 19:46:58
|
quote:
Originally posted by dc_calif
Even my larger tables have a high fragmenation level. I have a table with 6 million rows and did a reindex with a fill factor of 80% and within an hour it was back to 23% fragmentaion. Is that normal?
Depends on the indexes. If they are not monotonically increasing or decreasing, then fragmentation is normal.quote:
Originally posted by dc_calif
One other item that is a bit odd and might be a clue, I have 32 gig of memeory on the system and dedicated 20 gig to SQL yet when I look at performance monitor it never got over 2 gig. With all the users and all that was being asked its hard to beleive that it only needed 2 gig today.
Is your OS 32-bit or 64-bit? Also is SQL 32-bit or 64-bit?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dc_calif
Starting Member
5 Posts |
Posted - 2012-06-12 : 20:03:40
|
| I'm not sure where the performance issue is, i'm now pulling at strews and seen the high fragmenation and started to look at this and was hoping that it was the cause.The OS and Database are both 32 bit.1) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dc_calif
Starting Member
5 Posts |
Posted - 2012-06-12 : 20:46:43
|
| Ahhh, now that brings up another question.I'm running SQL 2003 R2 (Enterprise) for a x86 system supports up to 32 gig of memory which I have.I'm running SQL 2008 R2 (Entperise) 32 bit but I don't have AWE on. Do I need turn on 'Use AWE to Allocate Memory' to use this memeory or is there more to do here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dc_calif
Starting Member
5 Posts |
Posted - 2012-06-16 : 11:47:48
|
| OK so I found the problem, it was a memory issue.We have 32 gig of memory and AWE was not configured so only 2 gig was being utilized. Once AWE was enabled things got much much better.Thanks for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Performance issue.
Tara Kizer