Here’s this week’s weekly challenges….
- New release went out last Friday, 4/18/08 – lots and lots of SQL servers
- Two SQL 2005 Clustered 64 bit
- Two SQL 2005 64 bit Log Shipping servers
- Three SQL 2005 Utility servers
- Eight SQL 2005 Express servers
- One SQL 2000 utility server
- I spent most of the week in in the “War” room with a consultant from Microsoft.
- High CPU, slower performance issue on the biggest database
- We updated statistics with full scan and things were under control.
- Here’s some information on:
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
- Contiunied to get the performance testing environment up to speed and level set with production – all 16 SQL servers listed above are replicated in our performance testing environment.
Here’s this week’s challenges:
- Installed and configured SQL 2005 Reporting Services on two Servers.
- I kept getting an error when installing SQL 2005 on a different server. The source was a network drive that had the install on it. It would get to VSS writer and then bomb every time. As it turns out, the folder structure on the share wasn’t the same names as SQL 2005 expects, so it would fail when it would look for the Tools folder.
- I created a script that appeared to fail for users on a new server. We had one chance to test it and it sort of worked. Everything looked correct in the security folders – except the AD groups in the database – they weren’t mapped to the correct group. To quickly fix it, I put a check mark by one group, and hit apply – they were automatically put in there proper role – interesting I thought. I hit apply and 10 minutes later the user was back saying she still couldn’t do what she was trying to do. Upon further review, I went back to the same screne and it was the way I found it originally. As we investigated, we found there was some groups that got change from Admin to Administrators and such. There were schema that were getting mapped to the Admin groups and it wouldn’t let us add any more groups. Further investigation will follow.
- Worked on documentation for cluster installs and read up on changing the SQL Service to “Affect the group” in Cluster Administrator.
Solution of the Week – sent in from Ola of http://ola.hallengren.com regarding my post on Tuning sys.dm_db_index_physical_stats. Ola writes, “I’ve developed a solution for index optimization that is using sys.dm_db_index_physical_stats. I haven’t
seen the problem that you’re describing.
If you’re interested in my solution it is available on my blog. The stored procedure for index optimization does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB
existence and index size.
It’s been running in a large sql server environment for about 6 months.”
It’s quite an impressive, comprehensive and well done solution for index optimization.
Email at alohn at gmail dot com – if you’d like to have your site featured in the new feature “Solution of the week”
We had a report that was using sys.dm_db_index_physical_stats to find partition progress information. It was taking 45 minutes reguardless of the data in the results. We started looking at the data it was reading. The slow down was around two tables with LOB_Data in them. One table had about 14 million rows in it and has one column with a data type of XML and the other table has about a million rows and has a column with a data type of text. When SQL 2005 tries to run dm_db_index_physical_stats, it takes a long time due to these large column. Thus we changed the mode from LIMITED to SAMPLED which cut the time down from about 45 minutes to 2.5 minutes for those two tables.
This was another fun filled week with new SQL issues I hadn’t experienced in smaller environments.
1. I installed the wrong version of SQL on a server. It was a miscommunication between the network guy and myself as I read the OS column which was supposed to be Enterprise – I assumed it was SQL version column – anyway that was a couple of hours of work to fix.
2. I had to document all the servers post installation which mainly focused on some standard settings and some user login information. I’m planning a separate post for these issues.
3. I was asked to tune a report that was using sys.dm_db_index_physical_stats on a large DB. It was taking 45 minutes to run – if it returned data or not. I plan to write a separate post on this as well.
4. I found this blog from a guy who’s got some pretty good tips.