March 21, 2011 at 12:01 pm
1) ola.hallengren.com. Use the FREE goodness there.
2) remember to NOT update stats on indexes you rebuilt!! doing so gets you a) double work and b) WORSE statistics if you don't specify FULL SCAN
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2011 at 12:56 pm
Hey Jeffrey Williams and all,
I think you have all made good points and have shown me areas that I need to fix in my weekly maintenance plans.
I was running the mainteance plan update stats even after an index rebuild and that is wrong not unless I run sp_updatestats with the resample parameter or just not run an update stats at all. Is this correct? Does that hold true with SQL 2008?
I am also running an index rebuild on all the tables. I guess that's wrong to in that I should just pick those that need to be rebuilt instead of all. I'm curious what do you guys recommend for the % free space.
I'm looking into this link and what I can do to use ola.hallengren.com recommendations.
I haven't found a class on performance and tuning for SQL Server. Do you guys know of one?
You guys are awesome and thanks.:-D
March 21, 2011 at 1:06 pm
Patricia Johnson (3/21/2011)
I was running the mainteance plan update stats even after an index rebuild and that is wrong not unless I run sp_updatestats with the resample parameter or just not run an update stats at all. Is this correct?
If you run update stats maintenance plan task after an index rebuild make sure that the options 'column statistics' and 'full scan' are selected, otherwise you're wasting time updating stats that have just been updated.
I am also running an index rebuild on all the tables. I guess that's wrong to in that I should just pick those that need to be rebuilt instead of all. I'm curious what do you guys recommend for the % free space.
Yup, massive waste of time, resources and log space. Free space %? Really, it depends, there's no one good answer
I haven't found a class on performance and tuning for SQL Server. Do you guys know of one?
What's your budget?
http://www.sqlskills.com/T_SQLskillsImmersionEvents.asp
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
March 21, 2011 at 1:17 pm
Gail, isn't there a % that you can use that is "sort of" a best for almost all? I have over 250 databases or more and it would be helpful to have something as a quick for now and then go back and evaulate in depth.
I'm looking at that class. They will probably approve if it's a remote class so that I don't have to pay for airflight and hotel. Things are tight. 🙁
March 21, 2011 at 1:37 pm
http://www.solidq.com/squ/courses/Pages/Home.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2011 at 1:39 pm
Patricia Johnson (3/21/2011)
Gail, isn't there a % that you can use that is "sort of" a best for almost all?
No. You can pick some value as the standard and use that. Any reasonable value will do if you chose that. The smaller the %, the more free space in your indexes and the larger overall the DBs will be, the larger the % the more page splits you may get (depending on usage patterns of the tables)
I'm looking at that class. They will probably approve if it's a remote class so that I don't have to pay for airflight and hotel. Things are tight. 🙁
It's in-person. Honestly, unless you're half way around the world, the course is going to overwhelm any hotel or flight costs. It's probably one of the pricier courses. It'll be one of the best, but best does not come cheap.
I wasn't being totally serious, there are few companies I know of that would pay for that course. There'll be other training, but I'm not familiar with the training scene in US or Europe.
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
March 21, 2011 at 1:42 pm
Thanks Gail. I was looking at that class and you are right! Wow! That's my type of class. I would love to attend "all" of them not just one. LOL. Oh well. I would have to plan for it and make sure it's in the budget! Thanks again.
March 21, 2011 at 1:49 pm
Patricia Johnson (3/21/2011)
Thanks Gail. I was looking at that class and you are right! Wow! That's my type of class. I would love to attend "all" of them not just one. LOL. Oh well. I would have to plan for it and make sure it's in the budget! Thanks again.
If your company has a training budget like that, then *major envy*
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
March 22, 2011 at 8:32 am
I have a question about running dbcc checkdb on restored database(offline). Does it provide 100% confidence? For example, the DBCC CHECKALLOC part won't run at same environment. As a result you won't have 100% confidience about your consistency of disk space allocation structures of this database.
PZ
March 22, 2011 at 9:46 am
Please post new questions in a new thread. Thank you.
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
March 22, 2011 at 10:12 am
Gail,
The question was threaded from POST # 1080567 Grant Fritchey posted since he recommended to checkdb offline.
I cerntainly can create new thread but I feel it might be related.
PZ
March 22, 2011 at 11:03 am
If a checkDB of a restored database succeeds, you know that the production DB was free of corruption errors at the time the backup was taken. Allocation structures are backed up same as everything else in the database and restored the same as everything in the database.
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
March 22, 2011 at 11:23 am
Gail,
Thanks. In other words, if it ran successfully, the production is clear off the corruption as of the time backup took place. But if it failed, you wouldn't be certain if the database in production or the offline server corrupted.
PZ
March 22, 2011 at 11:28 am
Pei Zhu-415513 (3/22/2011)
Gail,Thanks. In other words, if it ran successfully, the production is clear off the corruption as of the time backup took place. But if it failed, you wouldn't be certain if the database in production or the offline server corrupted.
PZ
No, you still have to check the other parts of the allocation that aren't specific to the outer shell of the database. All the tables and indexes and the links between pages must still be checked. That can be checked offline on a seperate database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2011 at 11:40 am
Pei Zhu-415513 (3/22/2011)
But if it failed, you wouldn't be certain if the database in production or the offline server corrupted.
Correct. If it failed you'd have to check the actual prod database. But then checkDB successes should be far, far, far, far more common and if there's a failure the IO load on production to check it is likely the least of your concerns.
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
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply