Register here for the vWorkshop – Friday – June 5 – SQL Server 911
(Check out the course outline here, at the site)
Featured Article(s)
Troubleshooting SQL Server 2005 System Stored Procedures (Part 2)
In this article, Alexander Chigrik explains some problems that you can have when you use SQL Server 2005 system stored procedures. He also tells how to resolve these problems.
Performance Management – Feedback
The question was – for performance, do you "throw hardware at it" or "tune it?" Send in your feedback here.
Med: "Only by identifying the underlaying problem do you know which path to choose. Just killing it with hardware or having a tuning disorder is equally bad as it is a way to "expensive" solution. Throw 1 – 2 hours at it (identifying the problem) and then you will know which solution has the best pay-off. Stop guessing people, period!"
Michael: "I take the position of tuning first, then upgrading/adding hardware if tuning isn’t enough. Bad queries/indexes can’t be fixed by just thowing hardware at it.
For instance I have a side project dealing with making recording and playing back spatiotemporal data in a map-type UI. The GIS software I’m using first queries by lat/lon coordinates and then I can add my own WHERE clause. I found it much more efficient to query the other way around, by timestamp and then by coordinates.
All efforts should be made to first really think about not only how data is queried, but how often (in my case of playing back data) — that goes for writing data too (e.g write one row at a time as needed, or write at set intervals?). Once you’d optimized as best you can, then performance should only get better with better hardware. Otherwise, those performance issues will likely still be there, just masked by the new hardware."
Don: "[Dirty Harry] A SQL Server’s got to know its limitations.[/Dirty Harry]
Relative to the discussion about performance tuning v. more hardware, my experience tells me that if the hardware is appropriately provisioned for the given workload performance tuning is critical.
At one of the firms I’ve worked (actually a couple have a similar story) there was a production SQL Server which was not being maintained. They installed it, set up the databases and permissions, and ‘forgot’ about it (in that it was always there so there was no issue). I was hired there under the edict from management, "HELP!" I wish I could share the metrics with you but imagine an OLTP system in the insurance industry that never had a single reindex or updated statistic. Timeouts were nearly hourly, the server was sluggish and bogged very easily, and certain queries were very poorly written (yes, dynamic SQL w/o using sp_executesql and thus no execution plan); I looked like a big hero when I came in and implemented a smart maintenance routine which did all the things we’re taught in DBA 101.
Of course, the developer who was in charge of maintaining the SQL box contends the timeouts went away when we upgraded to SQL Server 2008. I conceded to him that the newest version of SQL Server had some internal improvements that would have helped, but it was the maintenance routines which brought us the greatest performance gains. Following that I rewrote the sprocs using sp_executesql until I could implement full-texting for search (which I’ve already done); next up is a smart table partitioning scheme. The server now runs like it’s skating on polished steel.
So, to add to the discussion (hopefully), having the hardware properly provisioned is important given a particular workload. I think that importance is overshadowed by performance tuning and daily maintenance; so long as the workload has not exceeded the hardware’s limitations an untuned, unmaintained SQL Server can pretty quickly bring a company to its knees."
We’re Establishing a User Panel – Interested?
We’re looking for only about 30 people that may be interested in joining our user panel. This panel will help review some ideas, thoughts and approaches to different features here at SSWUG.ORG. Active participation will be a maximum of 1 online meeting per month to review ideas and membership and/or vConference attendance will be provided as our thanks. If you’re interested, please send a note to me here and let me know the following in order to be considered:
– Are you currently a full, paying member of SSWUG.ORG?
– Have you attended a SSWUG.ORG vConference?
– Are you a full-time or part-time database professional?
– Where are you located (Country)? Timezone?
– Do you use SKYPE?
** There are no right answers, these just help us understand how best to possibly work with you.
Featured White Paper(s)
Migrating DTS Packages to Integration Services
SQL Server Integration Services (SSIS) brings a revolutionary concept of enterprise-class ETL to the masses. The engine is ro… (read more)