TempDB Configuration Matters

TempDB Database

TempDB Database

A re-posting originally on b2bsol.com

For each SQL Server database installation there are several accompanying system databases that help keep SQL Server running and functional.  They store information about security, what the server is doing, and how the system should process certain functions.  The TempDB database doesn’t keep permanent information–in fact, each time SQL Server restarts, the TempDB database gets purged and re-built as if it is a new database.  Why then should I care about the TempDB database?  Well, I am glad you asked.

Lots of Hats

The TempDB database has several functions it handles; some of which include: Sorting, local and global tables, index re-organizing, hash comparisons, XML Variables, spooling, triggers, snapshot isolation, and other internal functions.  In short–it is the workhorse of the environment and ALL databases on the system will interact with it at some point, so the configuration becomes very important.

Transaction Dependent

Because local table variables and hash joins are stored in the TempDB database, queries that use these objects/functions have a part of their processing done in the TempDB database.  When a query needs to move a process to TempDB, it creates a worktable and write data out.  What happens when I have 1,000 transactions per second and all of them have a hash join?  I will have a line out the door of threads waiting to be able to write to TempDB for their turn.  I can potentially have GAM (Global Allocation Map) and SGAM(Secondary Global Allocation Map) contention.  Also, because these processes are dependent for a transaction to process, the speed in which than can be written and read becomes important.

Providing some relief

There are two recommendations that can provide immediate to your TempDB environment.  The first is to create multiple TempDB files of the same size.  This will help alleviate the SGAM contention.  The second is to move the TempDB data files to the fastest disks in the system and/or to the most number of spindles as possible.  TempDB is the first candidate for Flash drives should the entire system not be able to take advantage of those disks.

How large should my TempDB files be? Determining the size of the TempDB files may be a bit of trial and error; however, if you have databases already on the system, one way to help make a decision is to run DBCC CHECKDB WITH ESTIMATEONLY to get the size the CHECKDB command uses.

How many TempDB Files should I have? While there are varying ideas, the one I hold to is 1 file per CPU core up to 8 files.  Monitoring will help provide insight if more are needed in the future.

The Importance of Backups

A re-post of a posting at b2bsol.com.

Too many organizations do not have adequate protection of their data and are susceptible to data loss.  While security is important and you might think this post is about implementing policy to limit your exposure to hacking, I am talking about something much more basic than that–I am talking about database backups.  A database backup is the first step in ensuring data availability and limiting exposure to corruption or human error.

What kind of backup strategy do you need?  Well, what is your tolerance for data loss?

To help you answer this question, there are two components you need to consider.

RPO – Recovery Point Objective

TechTarget defines RPO as “the age of files that must be recovered from backup storage for normal operations to resume if a computer, system, or network goes down as a result of a hardware, program, or communications failure.”  My definition would be something like–the moment in time you want to be able to restore to.  If you experience corruption or disk failure, how close do you need to get to that point in time?  Defining this metric–which will vary from system to system, will give you your RPO.

RTO – Recovery Time Objective

TechTarget defines RTO as “the maximum tolerable length of time that a computer, system, network, or application can be down after a failure or disaster occurs.”  My definition would be–The time needed to restore a system to usable functionality. I should note this time would include the alerting and response of your team if user intervention is required.

It would be easy to say, I want less than 1 second of data loss (RPO) and  less than 5 minutes of downtime (RTO); however, don’t expect to pay TGIF prices for Ruth Chris service.  Microsoft has made great strides in giving SQL Server many options for High Availability and Disaster Recovery and the ability to keep the system up ; however, none of these solutions remove the requirement to take backups.  The amount of history you keep will depend on your business requirements and the costs associated with keeping that storage.

If your organization does not have the RPO and RTO points defined, it is definitely time to make it happen.

Unable To Login to SQL Server Error 18456

I installed SQL Server on a machine the other day, I connected from my desktop, finished my normal server setup routine and life went on. Later, I connected to the machine via Remote Desktop, fired up SSMS and got this error message.
LoginFailure18456

With an accompanying error in the log
Login failed for user ‘MyAccount’. [CLIENT: ]
Error: 18456, Severity: 14, State: 11.

Very strange, I thought to myself. Turns out I had added my account to the local administrator’s group and not created a Login on the SQL Server. (It also appears I missed a step on my server checklist, as I normally remove the Builtin\Administrators group, but who is keeping score?)

I create the Login, and specified the group, and I was good to go.

CREATE LOGIN [Domain\Account] FROM WINDOWS;
EXEC sp_addsrvrolemember [Domain\Account], ‘sysadmin’;

Failure to calculate the default value

I was installing SQL Server SP1 on a one node cluster (long story) and I got this error “There was a failure to calculate the default value of setting PatchResult.” It turns out when I launched SQLServer2012SP1-KB2674319-x64-ENU.exe, it put the temporary folder–you know xcdswer-wersdf-weradf on a drive in the cluster. The patch restarted the sql server service rendering the drive from which the service pack was trying to install unavailable. I copied the temp folder to the local c:\ drive and I was able to install with no problems.

Newest Regional Mentor for PASS

This month I am pleased to accept the invitation to serve as a Regional Mentor for the Mid-Atlantic region of the PASS organization. It was not that long ago I stepped into my first chapter meeting in Richmond and discovered the unique mix that is the SQLFamily. Many local meetings, several SQLSaturdays, and the SQLSummit have only added to the experience and I am happy to be able to share some of these experiences with others.

As a regional mentor, I will serve as a resource for the local chapters in providing help as they operate their chapters. I will be looking to increase or strengthen the number of chapters in the region and maybe even add another SQLSaturday to the mix (looking at you Norfolk or Lynchburg). I am looking forward to it and as always–I will see you on the SQL trail.

Finding Active Heap Tables

As part of a review of a system, I look for tables without clustered indexes; however, invariably I will provide the list of tables to a development team and they will say “These are old tables we don’t use anymore”, so I am forced to provide additional statistics about the number of times a heap table is used in the database.  Based on a check found in Brent Ozar’s sp_blitz script, I pull usage information about the heap tables.  If there is no usage, they are not reported.

EXEC dbo.sp_MSforeachdb 'USE [?]; 
      SELECT DB_NAME(), t.name as TableName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_scan
      FROM [?].sys.indexes i 
		INNER JOIN [?].sys.objects o ON i.object_id = o.object_id 
		INNER JOIN [?].sys.tables t ON o.object_id = t.object_id
        INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id 
        INNER JOIN sys.databases sd ON sd.name = ''?'' 
        LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id 
      WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL 
      AND sd.name <> ''tempdb'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';  

Happy Hunting

Formatting Date Parameter In SSRS

DefaultValuesParameterMy report runs a stored procedure that accepts a date as a parameter. I want this date to have a default value of the current date–or the date when the report is run. I can do this by creating a parameter to put into my dataset. I then click on the Default Values tab and choose the Specify values option.

DefaultValuesParameterExpression

 

In the value drop down, I click the Fx button and enter the function of =Today(). This will give me a default date with no time. If I wanted the time included, I can use the =Now() function.

Installing SQL 2005 Service Packs

Now hopefully no one out there will need this post–especially because SQL Server discontinued support in 2011; however, because I learn something new everyday I feel compelled to put his post up. I came across a server that had automatic updates on and was trying to install SP2 for SQL Server 2005. It would error out and SQL Server would be shutdown. I was trying to install the update for SQL Server and the Analysis Server services.  The error in the log was
Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.
When I tried installing the service pack manually, this error displayed in the GUI.
Service Pack 4 for SQL Server Database Services 2005 ENU (KB2463332)'
could not be installed. Error code 1603.

The resolution was to delete some registry keys, which is still gives me the Hebe-geebees, and these were the keys.

SQL Server Entries

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\AGTGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\FTSGroup

Analysis Services Entry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\ASGroup

After I did this the installation went as expected. Now I just have to upgrade these bad boys.

Remotely Changing your Password

As I often work remotely from client sites and connect via VPN and Remote Desktop services, I am not in the position to run ctrl + alt + delete and change the password.  In order the change the password via a Remote Desktop session, you enter ctrl + alt + End and this will bring up the change password prompt.

Happy Trails.  :)

Creating a Linked Server to Oracle

I was recently asked to set up a linked server to Oracle and this is something I have done in the past.  No problem I thought.  To connect to Oracle you need the Oracle Data Access components and I has asked the Oracle DBA if they were installed.  He said yes and that everything was configured on his end.  I referenced David Browne’s blog for a quick review and ensured the Oracle directory was in the PATH variable.

I then proceeded to create the linked server got the following error–ORA-12514:TNS:listener does not currently know of service requested in connect descriptor

Oracle_HomeThere ended up being two issues–the first being, the TNSNames.ora file did not contain the entry for the database I was trying to connect to.  I guess the Oracle DBA didn’t think that was part of his setup.  Secondly, for some reason, the Oracle_Home variable was not set–it was blank.  I am not sure how that is possible; however, once I put the correct path in, I was golden and off and running.