SQL Server Performance Tuning

Configuring Files for TempDB database

TempDB database is a system database used to hold temporary user objects and row versions etc. IN addition, TempDB holds internal objects that are created by the SQL Server Database Engine.  TempDB plays an important role with respect to database operations. Since TempDB is used heavily, DBAs tend to allocate multiple data files to the […]

Finding Connections for A Single Database

During trouble-shooting you may want to find all the connections relevant to one database. You can use sp_who2 but the problem with that is it can return a lot of results for all connections and you need to scroll through entire result set. The following script will return connections relevant to one database with other […]

Troubleshooting High-CPU Utilization for SQL Server

The objective of this FAQ is to outline the basic steps in troubleshooting high CPU utilization on  a server hosting a SQL Server instance. The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. […]

How to Eliminate Unions After a Lookup Operation

The lookup is an essential component in SQL Server Integration Services (SSIS). In scenarios when a lookup is unable to find the correct match we should log the relevant record with some predefined value like NA or unknown. Let’s assume that we have a text file which contains employee identity numbers. Using these numbere we need to find the employee id in […]

How to Integrate Performance Monitor and SQL Profiler

SQL Server profiler displays data about a large number of SQL Server events. Whereas Windows System Monitor graphically displays information about the server internals. You can merge the two sets of information and walk through a scenario viewing both perspectives using SQL Server Profiler.   To set up the dual-perspective experience, you need to simultaneously capture server performance […]

Experiencing a major performance loss with an Indexed view that is using a INDEX SCAN on a table inspite of relevant clustered indexes on the joined tables?

Having a major performance loss with an Indexed view that is using a INDEX SCAN on a table inspite of relevant clustered indexes on the joined tables? A bit of background on this issue is first query doesn’t use indexed view. It uses index scan on table that has an inner join nested loops with IndexSeek […]

Microsoft SQL Sever management Studio: Failed to open connection dialog.

Error : Microsoft SQL Sever management Studio: Failed to open connection dialog. Even after reinstalling the client tools still the error persists.   Solution: This is a common question in the forums and newsgroups when any of the SQL Client tools gets corrupted. There might be chances that few times by reinstalling the client tools […]

How can I confirm whether a SQL Server installation is 32 or x64 bit?

How about to confirm the component type when a particular SQL Server 2005 instance has been installed? You can obtain the relevant information using a TSQL statement or referring to the SQL installation directory on that Windows server. Each of these options are outlined below: Transact-SQL statement: Execute select @@version from Query Editor or Query […]

How to set up database diagram in SQL Server Management Studio Express?

Problem:  I am SQL Server Express and after I created my database, I want to set up the database diagram using “new database diagram”, however I get error message : “Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties […]

Using SQL Server 2005 cannot Connect to a Remote SSIS Server?

Problem:When I’m trying to the MSDB database on a remote Integration Server (SSIS) using the integration services connection listed in the drop down box on SQL Server 2005 Management Studio.  Every connection attempt ends with an ‘Access is Denied’ error message.  Solution: For all Access Denied related error always ensure to check the relevant user has […]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |