Database user and programming tips
Have ever tried to debug a chunk of TSQL code (like a stored procedure) and found yourself trying to figure out which TSQL statement is at a particular line number? By default, SQL Server Management Studio (SSMS) doesn’t display numbers. But that is only the default, you can change it.
There are times when you might need to look at your SQL Server data in hexadecimal format. Occasionally some string data will contain unprintable characters, which can cause downstream problems, if not detected and removed. Read on to learn how to display and remove unprintable hexadecimal characters from SQL Server data.
When you are setting up a new SQL Server machine you need to determine how big you should make tempdb. To make sure you size tempdb appropriately you should monitor the tempdb space usage. If there are autogrowth events occurring after you have recycled SQL Server than you might want to increase the size of your tempdb data files. If tempdb never uses most of the tempdb space, then you might want to consider decreasing the size of tempdb.
There may be a time when you want to turn off transparent data encryption on one of your TDE enabled databases. In this tip, Greg Larsen shows you how simple it is to remove Transparent Data Encryption.
How many times have you had a programmer come to you and say they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted it? If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred. But in order to do that you need to know exactly when the programmer corrupted the data, which in a lot of cases is not known down to the second.
We all know that you should try to size tempdb appropriately, so it doesn’t need to autogrow shortly after starting up SQL Server. It isn’t always easy to do this. Therefore, when you first implement a new server and/or add new databases you should monitor the autogrowth events on tempdb. By monitoring the autogrowth events you can easily determine if you have sized tempdb appropriately.
When a database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full. Greg Larsen shows you how to find all database files that have their max_size set.
We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources. If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer. Those updates run longer because they have to maintain those unused indexes. If your application is not using an index, then there is no value in having that index. Read on to learn more.
Do all of your tables contain clustered indexes? Some say every table should have a clustered index. Greg Larsen shares a simple script to identify those tables in your database that don’t have a clustered index.
Why are you dropping and recreating your indexes, when you could just disable and then rebuild them? By disabling an index, you have basically turned off the index, but have allowed SQL Server to retain the index definition in the database metadata. If you want the index back all you need to do is rebuild the index.
Greg Larsen shows you a quick trick to programmatically script a way to identify when SQL Server was last started.
By looking at a query execution plan, you can determine which steps are used to resolve the query. But what if you just want to find out what the query plans are for only the queries that are currently running?
Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted. If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?
When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?” Read on to learn the many different states that a transparent data encrypted database might go through.
If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database.
Database User and Programming Tips Archives