Kategorie-Archiv: SQL Server

» Database Corruption Worksheet: Steve Stedman

» Database Corruption Worksheet Steve Stedman.

I was going to write that I’vebeen very lucky in not having to deal with any corrupt MS SQL Server Databases until now but, given the rock solid MS SQL Server, it’s not unusual to never see any corruption.

If I ever *do* have to recover an MS SQL Server Database, I’d be looking to start with the Worksheet from Steve Stedman and go on to trawling his and Brent Ozar’s and Paul Randall’s other stuff for methods.

AlwaysOn Availability Groups Real-Life Lessons Learned (Video) – by Brent Ozar Unlimited®

„AlwaysOn“ and „High Availability Groups“ were among the features that sounded just *so* great when first announced. Unfortunately, as insanely great as they sound and can be, there’s a lot to think about and to watch out for. Here’s Brent Ozar’s entertaining (as always) look at „Real“ Life.

AlwaysOn Availability Groups Real-Life Lessons Learned (Video) –  by Brent Ozar Unlimited®.

SQL Server Trace Flag 272 : Geek City: Lost Identity

I too used to belong to the crowd thanking Microsoft for the IDENTITY column type, I came from an MSACCESS background around about the end of SQL Server 7 (sic) and I was relieved to find I didn’t have to do any tedious „mucking around“ with identity value tables.

Of course, I also knew that there is no such thing as a free lunch and that SQL Server must be doing some housekeeping for me so it couldn’t be as performant as it would be with „raw“ data. But it was a heck of a lot faster than any T-SQL solution.

In SQL Server 2012 Microsoft, bless their cotton socks, „improved“ the IDENTITY column type and, naturally, in some minds broke it. Microsoft weren’t (all) born yesterday so they added a Trace Flag to enable you to get the old behaviour back. Nice. Here are some details:

Kalen Delaney : Geek City: Lost Identity.

SQL Server Trace Flag 8602

This one is *great* even if you probably might never need it.

That having been said, I do come across some queries written by hot-shots who use hints in their queries…. and they often don’t maintain the queries when the table structure has changed making the hints counter-productive. How could they remember where they have used which hints and know which ones to fix? How will they find the time to do so? Being able to demonstrate that the hints are no longer correct without changing the source is a terrific way of avoiding the „you must have changed something else as well“ kind of reactions from  certain types of people.

SQL Server Trace Flag 8602.

What risks are there if we enable read committed snapshot in sql-server? – Database Administrators Stack Exchange

Sometimes, you hear of something and you think „that’s just what I need!“ only to find that in the real world „things are a bit more complicated than you think“

READ_COMMITTED_SNAPSHOT

Is an example of this. At first sight I thought „why isn’t this the standard setting“ but this question-and-answer article might explain why…

via What risks are there if we enable read committed snapshot in sql-server? – Database Administrators Stack Exchange.

sql server trace flag 1118

„If you are still facing SGAM contention then you can use trace flag 1118“ it says in the article.

This flag is often quoted as one you should set in order to improve sqL Server performance. To be fair, the flag used to have a lot more significance than it does today but nowadays, before knee-jerk reflex applying the flag you should first look at the performance counters to see if you are actually being slowed down by the thing this trace flag changes.

The key here is to look at your tempdb usage to see if you are getting any PAGELATCH_EX locks on the tempDB. If you aren’t then adding this trace flag might actually negatively impact the SQL Server performance as a whole.

By the way, the „SGAM“ referred to in the linked article is „Shared Global Allocation Map“ so as you might guess it’s a map of memory which is shared so there might be some contention if two or more processes are trying to get at memory in the shared allocation map at the same time.

The bottomline is, as so often, take a baseline, make the change if you find some cause to do so, check the results, and keep or reject the setting as appropriate.

via sql server trace flag 1118.

How to Tell When TempDB is a Problem: Webcast Video – Brent Ozar Unlimited® by Brent Ozar Unlimited®

You might be able to tell that TempDB has been on my mind a bit lately. Unfortunately, TempDB is not the name of some person or social thing, it’s about work and the place where SQL Server stores its temporary stuff (and mine or any other SQL programmer’s stuff.)

In this webcast you get a pretty good idea of what you should do and look out for in your (if you have a SQL Server) Database environment. (And, nowadays, who doesn’t have a „database environment“?  … comeone, sure you do… or?)

How to Tell When TempDB is a Problem: Webcast Video – Brent Ozar Unlimited® by Brent Ozar Unlimited®.