SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
I am not an expert but I have been paying close attention to this area. This is just a primer. To me we should it break up into two areas: Real cloud computing and utility computing. However, they get lumped together because “The Cloud” is the such a buzz work right now. I think it is important to make the distinction. Thus the post.
Cloud computing
You can break cloud computing into two main areas. An end to end development platforms like Google AppEngine or the Windows Azure stack. The second bucket contains SaaS applications like Google Apps, MS Office online which is in closed beta right now or Zoho. We will not be talking about the SaaS apps.
As for as the DBA is concerned, the database is total abstracted. It is like your dev team is using an ORM but you do not manage the database behind it. This is where we get cut out.
Pros: No CapEX, less management
Cons: Immature technology, less flexibility
Utility Computing
Utility Computing is basically a virtualized instance of a OS. The administrator is still needed. It is just like a bare iron server install but the hardware is abstracted. It is transparent to SQL and the OS except for different hardware drivers. Companies that offer these services are Amazon with EC2, Terremark with Infinistructure\Enterprise Cloud and GoGrid.
Pros: No CapEX, more flexibility, full feature sets
Cons: Performance overhead do to virtualization. Licensing costs may apply.
Disclosure: I am employed by Terremark who offers utility computing.(even though we call it “The Enterprise Cloud :) )
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?
Technorati Tags: SQL Server,SQL Server 2008,SQL Server 2005,SQL Server 2000,tsql,cursors,virtualization,filtered indexes,social networking
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.