Posted by
Jason Massie 
on Friday, March 21, 2008 at 1:27 PM to
SQL Server 2008, SQL Shorts, SQL Server 2005, Windows Server 2008, tsql, Indexes, Offtopic, Virtualization, CTP6
356 Views |
1 Comments |
We regret to inform you of a passing in the SQL community. Read more here.
Rick Heiges has a post on my favorite new feature in SQL Server 2008, filtered indexes.
So does Decipherinfosys along with a good description on the difference of indexes and statistics if you need some background.
SQLBlogcasts has gotten an upgrade and Tony posts some great stats. Congrats!
Not SQL per say but the Hyper-V release candidate has been released. Speaking of Hyper-V, Sriram posts his slide decks on virtualizing SQL. Part 1 and Part 2.
This is a great starting point for SQL Server 2008 as is this. These come by way of the MSDN\Technet update blog.
The SQL Server 2005 sp3 debate continues.
The SQL ISV team posts a performance improving cursor rewrite sample. However, it is not ANSI compliant :) which is odd since most ISV tsql code needs to be portable.
Paul Nielson will be releasing a DVD.
While we are at it, check out the new SQL Server social network.
alt head: Got ADD?
read more
Posted by
Jason Massie on Wednesday, December 12, 2007 at 2:38 AM to
SQL Server 2005, Virtualization
181 Views |
0 Comments |
I have worked on two applications that were completely virtualized both on the front end and on the data tier. This post will cover my observations and experience. Both applications were similar. Both were newly developed ASP.Net 2.0 web applications with a SQL2005 x86 standard edition backend. They went with a virtual environment to avoid CAPEX. They both had 2 VPU, 2GB of RAM and a good HP EVA SAN. The databases were new so they were relatively small. The first one was tuning during a pre-production automated load test. The second was tuning a production work load.
I treated it as a normal tuning gig. I captured a perfmon log to determine which, if any, hardware subsystems were the bottleneck. They both turned up no disk or memory bottleneck. CPU averaged 70-80%. However, slow response times at the front end were reported. Upon further investigation, there was a CPU queue length, SOS_SCHEDULER_YIELD wait types, and runnable tasks averaging five or higher. We definitely had a CPU bottleneck. The only odd thing is we could not hit 100% CPU.
After some research, it appears that the CPU time counter may not be totally accurate. The full details can be found in this white paper.
“CPU usage data collected within virtual machines is not useful for two reasons. First, this data does not always accurately reflect the overhead of virtualization that is incurred by the ESX Server host. Second, because of the way time is kept within virtual machines, the usage data itself may be inaccurate(for details, see “Timekeeping in VMware Virtual Machines” in “References”). For these reasons, we use CPU usage data collected on the ESX Server host in this study. We used the esxtop tool to collect resource utilization statistics for VMware ESX Server. For further details, see “Appendix 2: Data Collection.”
Well, I went on tuning like a normal CPU bottleneck. We made substantial gains mainly through indexing, parameterization and caching at the web tier. We did nothing different because of virtualization.
Although I have yet to experience it myself, the white paper shows nice gains by using x64 especially if you need a multiprocessor environment so that is another thing to keep in mind. I would love to hear your experience.