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 was going to try to go through my google reader shared items from this year and try to come up a list to end all lists. However, who am I to decide? On top of that, narrowing it down to a top 25 favorites was going to be a enormous task given the huge growth in quality SQL bloggers this year.
However, instead of giving you a list, I will give you a tool to find good SQL blog posts based on the collective conscience on the internet. Hopefully, you will see the value and join in making this tool better.
Popular Posts on “SQL Server”(RSS) from RSSMeme.
Behind the scenes in a nut shell
Blog software syndicates via RSS. Readers subscribe in google reader or their favorite reader but only google reader applies here. When a user in google reader finds a post interesting, he\she can share it. All of the shares are indexed by RSSMeme. The idea being is a human actually recommends the post. More shares the better. Sure, there are also sites like Digg and Dotnetkicks but the down sides to those make them less appealing to me.
A side effect
Using this link you can find recently shared items containing “SQL Server”. This can help you find new interesting feeds. You can also use it to find breaking new on SQL Server or whatever tickles your fancy. However, it may not be quite as real time as twitter.
Start sharing
So if you use google reader and don’t use the share functionality, start! And maybe, you too can be part of the Internet SQL Mafia ©. Westside.
Top Sharers
P.S. Congrats to the Detroit Lions for their perfect season.
TokenAndPermUserStore cache bloat has been a problem for quite a few of my customers. It is hard to diagnose unless you have run across it before. The queries are just slow without a hardware bottleneck. There were enhancements in sp2, post sp2 hotfixes and trace flags to help out. To be fair to MSFT, most of the time, the root cause was bad plan reuse.
In SQL Server 2008, this memory clerk is configurable with sp_configure. Yay! However, in SQL Server 2005 SP3 you have a few new options detailed in this KB.
This is also one other noteworthy enhancement in SQL Server 2005 SP3
Note In SQL Server 2005 Service Pack 3 and in later service packs, the processor time stamp is not used. These versions of SQL Server 2005 use a more reliable timer that has a maximum precision of 1 millisecond.
The full article can be found here.
Edit: Bob Dorr has posted a more in depth explanation of the time change here.
This is my 2nd installment of cool new(at least to me) sql blogs. I am trying to focus on ones that have good content and may not being getting enough exposure(by my guess). Not that I can slashdot your blog or anything but links are good.
If you don’t end up here, it could be for several reasons:
In no particular order:
1. Jim McLeod (WWW, RSS)
Recent highlight: Cluster Won’t Start Due to Incorrect Startup Parameters
2. Joe Sack (WWW, RSS) – Not new to me but a good one.
Recent highlight: Windows Server 2008 - How to Move the Quorum Disk Group
3. The Database Whisperer (WWW, RSS)
Recent highlight: Adding a Check Constraint to a View
4. Chris Shaw (WWW, RSS)
Recent highlight: SQL Quiz Part 2
5. Aaron Lowe (WWW, RSS)
Recent highlight: SQL Quiz: Toughest Challenge
6. Deepak - (WWW, RSS)
Recent highlight: SQL Server 2005 Default Trace
7. SQL in the Wild (WWW, RSS) Not new but you should be reading
Recent highlight: When is a critical IO error not a critical IO error?
8. SQL Ninja (WWW, RSS)
Recent highlight: Ongoing Education for the Terminally Lazy/Busy
9. You Want Fries with that? (WWW, RSS)
Recent highlight: SQL PASS Community Summit 2008
10. Glen Berry - (WWW, RSS)
Recent highlight - Excellent Results With SQL Server Native 2008 Backup Compression
I tried to create cool web 2.0 blog roll. It worked for a while but it is hosed.. It would take me too much time to recreate it. Not to mention blogs come and go so I am going to do three things.
So post interesting things and you get a link from me in several place. Woohoo. Yah, big deal, right? If I am missing you, let me know. The amount of good SQL blogs is growing exponentially and it is hard to keep up.
NOTE: IF YOU ARE ONLY POSTING AN RSS SUMMARY, I AM PROBABLY NOT READING YOUR BLOG!!!11WON
Here is a Windows setting that often doesn’t play well with SQL Server. There is not much info out there so I thought I would blog about it.
There are three sets of problems that I have run into that can come from it:
1. Network disconnects. It manifests at the client with these errors: Reference kb945977.
Error message 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation Error message 2 ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure Error message 3 System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Error message 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation
Error message 2 ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure
Error message 3 System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Do any of those ring a bell? :)
2. High NonPaged pool usage with some NIC’s. Reference kb918483. This manifests with this error message.
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
3. I suspect that a mysterious periodic cluster failover was caused by this. I cannot prove it though.
To disable this setting, run Netsh int ip set chimney DISABLED and reboot. You should do this at the client and SQL Server if you have problem 1. With problem #2 when you are on SQL Server standard edition, you might as well try it because if it doesn’t fix it you only have a few other options. Chances are that the only fix is an upgrade to Enterprise Edition.
There might be a good reason to leave it enabled in some situations but I am not sure what they are. It has caused me enough problems to turn it off by default.
Edit: Doh, Sara Henwood wrote a similar but better post on this issue. I wish I would have saved it to gray matter back when it came out.
I have found a lot of new SQL blogs lately. Here are the top 5 top 10 in no particular order. I am going to try to make this a monthly post.
While they may be new to me, they may not be new. I am going to try to stick with ones that A) have good content and B) may not be getting enough attention. It is hard for me to judge B. It mostly just based on my perception which could be wrong. :)
So if you are not on the list don’t take offense. Nothing personal but your blog sucks. Just kidding!
1.
SQL Fool (WWW, RSS)
Author: Michelle F. Ufford
Recent Highlight: Performance Comparison of Singleton, XML, and TVP Inserts
2.
Facility 9 (WWW, RSS)
Author: Jeremiah Peschka
Recent Highlight: Mirroring table changes through DDL triggers
3.
TJay Belt (WWW, RSS)
Author: TJ Belt
Recent Highlight: #PASS Board of directors candidates
4.
The Rambling DBA (WWW, RSS)
Author: JONATHAN KEHAYIAS
Recent Highlight: What am I reading? : SQL Server 2005 Practical Troubleshooting: The Database Engine (Ken Henderson)
5.
Home of the Scary DBA(WWW, RSS)
Author: Grant Fritchey
Recent Highlight: PASS Board Elections
6.
SQLBatman (WWW, RSS)
Author: Bruce Wayne aka Thomas LaRock
Recent Highlights: PASS Elections
7.
Kendal Van Dyke (WWW, RSS)
Author: Kendal Van Dyke
Recent Highlight: Delegation: What It Is And How To Set It Up
8.
SQLAGENTMAN (WWW,RSS)
Author: Tim Ford
Recent highlight: SQLSERVERPEDIA WIKI
9.
Arcane Code(WWW,RSS)
Author: Robert C. Cain
Recent Highlight: SQL Saturday Orlando Full Text Searching Session
10.
SQL Dumbass (WWW, RSS)
Author – ?
Recent Highlights: Backups and Boat Drinks
I have run into this several times lately so I thought I would whip out a quick post.
The scenario
A query runs much faster with a hash join hint than a nested loop but that is what the optimizer is choosing.
High level background
Merge and hash are good when you are working with lots of rows because it can leverage sequential IO. If you do a nested loop join with lookups on 100-1000+ rows, the random IOs can be 10-1000% more expensive than a sequential scan.
Why?
So why does the optimizer choose a nested loop? I have seen it is because the optimizer had to make a best guess at how make rows were being returned. It most cases, the optimizer guesses 1 row will be returned so a nested loop is the best choice as far as the optimizer can tell.
Identifying the problem
Identifying the problem is pretty easy. All you need to do is look at the query plan. It can be text, xml or graphical. Look actual and estimated rows returned. If the actual rows are 11teen,000,000 but the estimated rows is 1 then this may be the problem
Causes
I most often see this when large table variables, local variables or modified stored procedure parameters are involved. There can be other causes. See my favorite white paper.
Fix?
Easy. Don’t use table variables and local variables. If you have to modify sp parameters, pass them to a nested stored proc. Make sure stats are up to date. If the WHERE cause is complex, try putting the logic in a persisted computed column or indexed view. Another option is to use a covering index. Of course, the last resort is the JOIN hint.
Need more?
Want to get real deep on stuff like this? Check out Craig Freedman’s blog or his Inside SQL Server 2005 chapters.
I quote “wrong” because with the info the optimizer has, it thinks it has the right index. :)
1. Look for cardinality problems. You can do this by looking at the actual rows versus estimated rows in the query plan. If they are off, chances are you are not following the best practices listed here.
2. Conversions in the WHERE clause can cause issues. Be sure to look at query plan for implicit conversions
3. Complicated logic in the WHERE clause can also cause problems. Consider indexed persisted computed columns or indexed views.
4. Constraints give the optimizer more info when choosing a plan with the “right” index. It can take a PK or unique constraint into consideration but it does not know about similar logic in a trigger.
5. Large datasets are being returned but the index is not covering. An index with just the columns in the WHERE clause is ok if just a few rows are returned but it does not take much for an index scan to be more efficient.
#1 is the most important one because the link gives 5 or 7 more reason why the “wrong” index is chosen.
P.S. If all else fails, update stats and if that does not work, make the index covering.