Entries for the 'SQL Server 2005' Category

Best SQL Server Blog Posts of 2008

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

• StatisticsIO
• Brent Ozar
• ttoennies
• Jeremiah
• Rob Boek
• Bogdan Brinzarea

P.S. Congrats to the Detroit Lions for their perfect season.

email it! |   |   | 

Permalink     2 Comments

TokenAndPermUserStore and a New Timer in SP3

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.

email it! |   |   | 

Permalink     0 Comments

Good SQL Blogs from around the Tubes.

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:

1. You blog is part of a large established aggregations like SQLBlog, SQLBlogcast, SQLTeam or SQLServerCentral.
2. Your blog is an MSDN blog. I suggest you check this periodically.
3. Your site is already high profile with lots links etc..
4. I might not know about your site. Let me know.
5. Most of your posts are not tsql or database engine related
6. Yer mamma dresses you funny.

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

email it! |   |   | 

Permalink     5 Comments

My Blogroll is Jacked

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.

• Share from google reader interesting(to me) posts. These will show up in my friendfeed widget on the left. You can also subscribe to my shared items here.
• Friend me on Facebook because they show up there too.
• I am going to do a monthly post on new SQL blogs that I have found that are not getting a lot of exposure but have great content.

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

email it! |   |   | 

Permalink     1 Comments

TCP Chimney Offload

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.)

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.

email it! |   |   | 

Permalink     1 Comments

New\Newish SQL Blogs(at least to me)

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

email it! |   |   | 

Permalink     4 Comments

Why do I have to use a MERGE\HASH JOIN Hint?

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.

email it! |   |   | 

Permalink     4 Comments

5 Quick Tips for the Query using the “Wrong” Index

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.

email it! |   |   | 

Permalink     1 Comments

This Update Came from SQL Server