contactsam
asked on
Slow performance on Microsoft SQL Server 2008
Hi,
In current production database of Mcirosoft sql server 2008, we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
Even after performing the above task, the application performance had degraded.
New to the Microsoft sql server world, please recommend any pointers to improve the performance.
Regards
In current production database of Mcirosoft sql server 2008, we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
Even after performing the above task, the application performance had degraded.
New to the Microsoft sql server world, please recommend any pointers to improve the performance.
Regards
>> we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
Kindly confirm whether you have also shrunk the data file after this activity or not..
>> After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
If you haven't shrunk your data file, then ideally Reorganizing the tables and indexes aren't required..
Kindly confirm whether you have reorganized or rebuilt all tables and indexes on your database or with some fragmentation limit criteria..
>> Even after performing the above task, the application performance had degraded.
If you have reorganized without considering the fragmentation values, then it would be better to check the Ola Hallengren script shared by pcelba above..
Also reorganizing all tables and indexes would force your application queries or SP to choose a new execution plan for the first time execution after your Reorganize activity so it would take some time to settle down till new optimal execution plans are created for all queries.
Kindly confirm whether you have also shrunk the data file after this activity or not..
>> After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
If you haven't shrunk your data file, then ideally Reorganizing the tables and indexes aren't required..
Kindly confirm whether you have reorganized or rebuilt all tables and indexes on your database or with some fragmentation limit criteria..
>> Even after performing the above task, the application performance had degraded.
If you have reorganized without considering the fragmentation values, then it would be better to check the Ola Hallengren script shared by pcelba above..
Also reorganizing all tables and indexes would force your application queries or SP to choose a new execution plan for the first time execution after your Reorganize activity so it would take some time to settle down till new optimal execution plans are created for all queries.
ASKER
Hi Jegan,
Thank you for your communication. In response to your queries
Q- Kindly confirm whether you have also shrunk the data file after this activity or not..
A- Shrunk the data file had not yet been performed.
What we have performed so far
a) Truncate the tables approx 110 MB instead of GB (Sorry it was typo)
b) Reorganize Index tasks (Tables and views)
c) Update Statistics task - Objects (Tables and Views) Full scan
Based on the above tasks performed, please let us know the next steps to be performed.
Regards
Tom
Thank you for your communication. In response to your queries
Q- Kindly confirm whether you have also shrunk the data file after this activity or not..
A- Shrunk the data file had not yet been performed.
What we have performed so far
a) Truncate the tables approx 110 MB instead of GB (Sorry it was typo)
b) Reorganize Index tasks (Tables and views)
c) Update Statistics task - Objects (Tables and Views) Full scan
Based on the above tasks performed, please let us know the next steps to be performed.
Regards
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the update.
We were planning to perform shrink data file during the week, based on your last update now it looks to be optional.
The maintenance plan was constructed through the wizard. There were no scripts involved.
Screenshot is attached with this update.
Regards
Tom
screenshot_Maintenance.rtf
We were planning to perform shrink data file during the week, based on your last update now it looks to be optional.
The maintenance plan was constructed through the wizard. There were no scripts involved.
Screenshot is attached with this update.
Regards
Tom
screenshot_Maintenance.rtf
I couldn't see the attached rtf file properly..
Kindly confirm whether you left the wizard with the default values or modified anything..
Kindly confirm whether you left the wizard with the default values or modified anything..
ASKER
If that is left with the default values, then it will reorganize indexes only greater than 15%..
It shouldn't cause any performance issues, kindly confirm the below
1. whether truncating this 115 MB log table is the only activity you have performed or something else.
2. How was the Server performance before and after this activity. Was it performing well before this truncate activity..
It shouldn't cause any performance issues, kindly confirm the below
1. whether truncating this 115 MB log table is the only activity you have performed or something else.
2. How was the Server performance before and after this activity. Was it performing well before this truncate activity..
ASKER
Hello Raja,
Response to your queries
1) No other tasks were performed after truncating 115 MB Log table except for running the maintenance plans first time with default values,
during the business hours. It was observed slowness during that time so we cancelled the job and re-executed during the night hours that ran for 8 hours successfully.
2) Server performance was reasonably good before the activity, after wards when we cancelled the maintenance task the performance degraded. Right now the performance looks to be good.
Regards
Tom
Response to your queries
1) No other tasks were performed after truncating 115 MB Log table except for running the maintenance plans first time with default values,
during the business hours. It was observed slowness during that time so we cancelled the job and re-executed during the night hours that ran for 8 hours successfully.
2) Server performance was reasonably good before the activity, after wards when we cancelled the maintenance task the performance degraded. Right now the performance looks to be good.
Regards
Tom
Yes, all maintenance jobs must be scheduled to night hours otherwise you observe performance hits in the prime time.
The data file shrinking is contraproductive. It causes data fragmentation most likely and you should do it when the free disk space is low.
The data file shrinking is contraproductive. It causes data fragmentation most likely and you should do it when the free disk space is low.
>> after wards when we cancelled the maintenance task the performance degraded. Right now the performance looks to be good.
Seems to be that your daily Maintenance tasks were cancelled on that day and today its schedule might have completed making it perform better.
If there were any activities causing your Maintenance jobs to be stopped, just execute the maintenance tasks after your activities are completed once..
Seems to be that your daily Maintenance tasks were cancelled on that day and today its schedule might have completed making it perform better.
If there were any activities causing your Maintenance jobs to be stopped, just execute the maintenance tasks after your activities are completed once..
ASKER
Thank you Guys for all your help
Regards
Regards
Welcome, glad to assist!!
Both reorganization/reindex and statistics update should be a part of the regular maintenance task. You may look e.g. here to download the tool and learn more about it: https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/
To look what's is the performance bottleneck you have to study much more... Maybe your disks are slow or just some indexes are missing.
You may start here: https://www.brentozar.com/first-aid/
Then you may continue to study wait states and other fine tuning hints.