How To Fix Invalid Object Name - Database Context - In SQL Server

 Well, this becomes the most used and seen error in SQL Server if you are working as a database developer or a DBA. Additionally, most of you must already be aware of the situation of how to react when you see the error as “Invalid object name”. But there are places where some of you may get hung on what next when you see the error.

The fix for the error – Invalid object name, due to database context is pretty straight forward. Most of the users who explore SQL Server initially will come to use the GUI more than using the script itself.

Here, there are chances that you might have opened a new instance that selects the master database by default but fetching the records of your desired kind. So, the user may just think of havoc in this situation.

For demonstration purposes, I have created a table as [testing] and trying to run in master. So, we are obvious here that we are going to get the error as – invalid object name, as we are in the wrong database. 

Fix Invalid Object Name SQL Server 1

Fix:

Immediately I changed the database name to the database where my table exists, the error is gone. 

Fix Invalid Object Name Database Context SQL Server 2

Always make sure to check the database context while opening a new database instance as they tend to open the master database by default. 

If the issue still persists, then it could be due to IntelliSense Cache not refreshed. Please click here for a fix.

For your reference: How To Fix - Invalid Object Name - String_Split 

How To Compress A Native Backup In SQL Server

 The compressions of backups in SQL Server is purely to improve the performance of the server itself. The idea behind a backup compression is to reduce the disc usage in your drive. I was working with one of my friends who is a recent explorer in SQL Server and wanted to gather information on backup compression. It would be a good time to fabricate the native backup to all my SQL friends.

The native backup compression feature has been in use right since the SQL Server start-point. However, the compression was available only in the Enterprise Edition of SQL Server 2008 or earlier. If you are using any of the SQL Server 2008 or older version, then the backup compression is going to work only on Enterprise Edition. Further, the backup compression feature is made available starting from SQL Server 2008 R2.

The usage of native backups in SQL Server is still optional and can be performed using a third-party too. While comparing the performance of the native backup with the third-party tool, the third-party tool performs much better.

However, let’s check on how to compress a native backup in SQL Server using GUI.

Step 1: Open the SSMS and Right-Click on your server.

Step 2: Navigate to Database Setting and check the Compress backup option

Compress Native Backup SQL Server 1

You are all set with enabling the native backups with compression. 

Once we are done with enabling the native backup compression option using GUI, we have another method to perform backup compression through the script. 

USE [master]
GO
BACKUP DATABASE [DBCompression_Testing]
TO DISK = N'D:\Backup\DBCompression_Test\NativeCompressionBackup.bak'
WITH COMPRESSION, STATS = 10
GO


Compress Native Backup In SQL Server 2

Using the script is simpler and more efficient than using the GUI. So personally, I prefer to run the backup through scripts than using GUI. However, both options are well enough to use.

For your reference:

How to create a full backup
How to create a differential backup
How to create a log backup

7 Simple Performance Tuning Tips And Tricks In SQL Server

Simple Performance Tuning Tips and Tricks SQL Server

We all come across a lot of applications in our life. Also, there are a lot of places we admire at the application performances as well. The main factor for any application to perform at blazing speed would be to improve the database performance in the first place. Database plays a vital role in application performance. If the front-end is well-developed but the database is sluggish, then the whole effort of development would go for a toss. In such cases, performance tuning comes to the picture. Additionally, while we are increasing the performance of a query, we should start with the basic and simple steps. Always it’s the best practice to check the simple and the simplest of the tuning before even stepping into the advanced indexing strategy.

While I have worked on many queries to improve the performance with some of my junior folks, the first intent they get is adding up an index. To be honest, adding an index can degrade the performance. However, I will be sharing performance tuning for indexes in the future.

Further, through my course of improving queries, I felt that these 7 simple tips and tricks can help to a greater extent. Let’s check the 7 performance tuning tips and tricks for our SQL Server database that can probably avoid problems.

1. Avoid SELECT * FROM

The SELECT * FROM is a sin in SQL while considering the performance. It is going to be definitely a bad thing if you are seriously working on developing a full-fledged application for a high-traffic interface. The SELECT * FROM is a Hitman for SQL Optimizer. There is no chance for the SQL Optimizer to choose the underlying indexes for a SELECT * statement.

Area of Tuning: 

Try to SELECT the column names needed for the data retrieval. This is going to help avoid disk, network, and memory contention on your server.

2. Caching In Application

If your application is randomly hit by multiple users across the globe and the data retrieval is happening directly from the server, then it is a bad idea. Every time a user retrieves the data from the database, there is going to be a huge amount of disk rotation and network contention. Additionally, this phenomenon can cause the server to go slow eventually.

Area of Tuning: 

Encouraging to cache in the application memory, to avoid constant queries to the database. This caching method can be implemented on infrequent updates and small sets of data such as lookup values.

3. Keep Transactions Short

This happens in many industries wherein the queries are Adhoc. These Adhoc queries could be to fix the application problems such as data retrieval and other reporting strategies. Basically, some of the reporting queries can run so long that the entire server’s CPU is eaten up. In fact, this is going to ruin the entire server’s performance. 

Area of Tuning: 

Try to shorten the query execution time and keep them simple. Longer queries are going to take way long locking on tables. The higher locking, the more the next user waits for accessing the table. Along with the lock waits, there could be a spike in CPU usage if the transactions are running for a longtime. Use a precise locking strategy to overcome blocking in SQL.

4. Avoid NOLOCK Hint

It is very general for most of the DBAs working on SQL environment to use the NOLOCK hint on big tables on a production server to avoid blocking. Usage of NOLOCK hint breaks the fundamental rules of data integrity. Additionally, using the NOLOCK hint can lead to missing or extra rows in data sets, which is a huge abuse to SQL. 

Area of Tuning: 

We have an alternate version to deal with the NOLOCK hint introduced by Microsoft, a snapshot isolation level. Using the command such as READ_COMMITTED_SNAPSHOT isolation level on top of any of your queries can simply solve most of the SQL overhead problems. Further, this can as well solve the missing or extra rows in data sets.

5. Use Variable And Parameter Data Types Same As Columns

This can be something interesting for people on how the data type slow the performance of a query. While I was testing the data import on a stored procedure on one of my optimization projects, there seemed a mismatch in data type between the declared variable and the columns. When the data is imported from a variable to a column with different data types, there is going to conversions happening. This can hit the SQL optimizer to a greater extent.

Area of Tuning: 

Try to keep both the variable/parameter data types with respect to columns always the same. This can help avoid table scans and as well void the cause of data conversions.

6. Avoid Usage Of DISTINCT, ORDER BY Or UNION Unnecessarily

It becomes very common for us to eliminate the duplicates using the DISTINCT function. A DISTINCT can be a killer for SQL. Additionally, the ORDER BY is going to sort your query, and internally optimizer is thinking of more logical ways to do that. This in turn of sorting your query will be a time taking job. Further, the UNION is one simple way to combine results. The usage of UNION can just slog the server over time. 

Area of Tuning:

DISTINCT: Instead of using the DISTINCT function, try with GROUP BY clause at the end of your query to eliminate duplicates. The reason for this changeover is simple and clear that the SQL is going to do a bit of more job internally if used as DISTINCT. This can be viewed in a precise manner while using the DISTINCT on big queries. 

ORDER BY: Now it's going to be your call on either to use a GROUP or BETWEEN operator to get the range. The logic on your query should be optimal enough to make the SQL feel much better.

UNION: Please refer to the Advantagesof UNION ALL Over UNION for your solution of replacement.

7. Minimize CURSOR and WHILE LOOP Usage

CURSORs and WHILE LOOPs usage may be one of the Database developers or DBA’s personal choice. But as per my research on the CURSORs and WHILE LOOPs, the performance was degrading over time. This is because the CURSOR and WHILE LOOP go with row by row basis and it is time-consuming task. If you are developing your application that needs a faster result and are working on CURSOR and WHILE LOOP basis, then it’s a bad idea.

Note: Usage of CURSOR/WHILE LOOP can be seen with good performance on small data sets. 

Area of Tuning: 

Try to use the sp_msforeachdb system stored procedure. The sp_msforeachdb is commonly avoided by some junior folks. This is because it becomes a tedious job to go with the internal coding of sp_msforeachdb stored procedure. But, please check in the long run of your application and it would be much appreciated.