SQL Performance
There are 6 entries for the tag
SQL Performance
This is nothing new or revolutionary, but is important nonetheless for those maintaining large-scale SQL Server databases. For years I've used the sysindexes system table to find out how many rows are in a large table. The reason for this is to prevent table scans of large tables just to find out how many rows are in that table.Sometimes you just gotta know and you don't need a custom filter, just the raw row count. If you use SELECT COUNT(*) FROM myTable you scan the whole table affecting the performance of your server.For those who've never heard of this here's...
The load balancer I wrote last year never made it into full production. While it was still in Q&A we hit our deadline and we stopped new development as we hit our "busy season". At MaxPreps, once the fall season starts and football is in full swing our architechture gets locked in and we don't make changes to it unless it's to fix problems we missed. So the load balancer went by the wayside. But now it's the time of year where we start preparing for the next year and so it's time to dust of the load balancer and...
I've started investigating the SQL CLR and decided as my first article to tie in a bit to my article on using SQL parameters in ad hoc queries. This time I'm touching on an advanced topic of sql parameters - how to pass an array as a parameter. This takes advantage of the performance benefit you get from parameters because the plan can be cached for queries that use dynamic IN statements.http://www.codeproject.com/cs/database/TableValuedFnsAsArrays.aspNext, I plan on writing a series of articles on the SQL CLR discussing the following topics:CLR SecurityCLR Performance/StabilityCLR vs T-SQLCLR Best PracticesI've almost finished the first article on...
As I look around the net at different articles and tutorials I'm surprised at how many use Dynamic SQL for samples. Even after the many warnings about SQL injection attacks. But there are other issues besides security. One of which is performance - dynamic SQL performs very poorly when not used properly. So I decided to write an article pointing out how poorly dynamic SQL can be if improperly used. I hope it will convince some to change how they write their client SQL code. Here's the link:http://www.codeproject.com/cs/database/ParameterizingAdHocSQL.aspI might even write another article that expands on this idea to put...
I have been working on a "load balancing" application for OLAP applications and one or two of my previous posts touch on some things I've learned in the process. But now that I've got a working beta version I've decided to write my first "formal" article based on what I've learned. I've posted the article on my favorite developer community site, codeproject.com. Here's the link:http://www.codeproject.com/useritems/dbloadbalancerservice.aspThe article is written based on using an ASP.Net application as the client for the load balancer, but in reality the client could be windows based as well.Tags: SQL Performance, SQL Server
SQL Profiler has been aptly referred to as the "poor man's load tester".3 months ago I captured trace logs spanning 1 week. My intent was over the summer (our companies "off-season") to use the trace logs to stress test the database to determine the results of server and database configuration changes. Now I'm spending most of my morning trying to replay the trace logs I created using the system stored procedures 3 months ago.Well, here's what I've discovered:The database ID in the trace log has to match the database id of the database on the server where you are replaying...