SQL Server performance Tuning - 1

SQL Server performance Tuning - 1

1: What is the use of profiler while dealing with performance issues?

I think SQL Server Profiler is one of the must have tools while dealing with performance issues. This tool is available with SQL Server since a long time. SQL Server Profiler can perform various significant functions such as tracing what is running and finding out how queries are resolved internally. The major functions this tool can perform have been listed below:

• Creating trace
• Watching trace
• Storing trace
• Replaying trace

2: Should you always leave a profiler trace running on a production server? If yes, then why?

One should never leave profiler trace running on a production server. Profiler adds too much overhead to production server. And Profiler collects surplus of data making it extremely complex to analyze them afterwards.

3: Will you advise to leave a profiler trace running on forever from Server A to B?

No, one should never leave profiler trace running on forever on server. It hardly matters if a profiler trace is running from A to B or directly on B.

4: What is server side trace?

Any time you open SQL Server Profiler and run a trace, you’re running a client-side trace. To run a server-side trace, one needs to create a script. Server side trace sometimes provides more flexibility as compared to profiler trace. These four procedures can be used to create Server side traces.

• sp_trace_create
• sp_trace_setevent
• sp_trace_setfilter
• sp_trace_setstatus

5: How can DMVs help with performance tuning?

• Helps to find out the queries that are causing memory or CPU pressure on your system
• Helps to investigate caching, and query plan reuse
• Helps to identify index usage patterns
• Helps to track fragmentation in clustered indexes and heaps
• Gives full details on blocking and blocked transactions

6: Can you please name three DMVs that you have used recently with respect to Performance Tuning?

dm_exec_query_stats

dm_exec_sql_text

dm_exec_query_plan

7: Why is it important to avoid functions in the WHERE clause?

The outcome of any function is evaluated at the run time, so the SQL Server Query engine has to scan the whole table to get necessary data.

8: What are indexes?

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.

9: What are the different types of indexes in SQL Server 2012 Version?

• Clustered
• Non clustered
• Unique
• Column store
• Index with included columns
• Index on computed columns
• Filtered
• Spatial
• XML
• Full-text

10: What is Index with included columns?

A non-clustered index that is extended to include non-key columns in addition to the key columns.

11: Can you create a Non cluster index on primary key?

Yes, by default a clustered index is created on primary key. However, a non-cluster index can be created on Primary Key.

12: What is index Seek?

Index seek is an operation that only touches rows that qualify and pages that contain these qualifying rows.

13: What is index Scan?

Index scan is an operation that touches every row in the table whether or not it qualifies.

14: What is Caching Mechanisms?

SQL Server avoids compilations of previously executed queries by using these four mechanisms to make plan caching accessible:

·         Adhoc query caching

·         Auto parameterization

·         Prepared queries

·         Stored procedures or other compiled objects

15: How can you clear plan cache?

Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server.

16: Give some examples of DBCCs related to SQL Server Cache?

DBCC FREESYSTEMCACHE

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

17: What is Max Degree of Parallelism or MAXDOP?

If there are more than one processor or CPUs on a server where SQL server is running, SQL server detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution.

18: How do I configure the Max Degree of Parallelism at the server level?

MAXDOP Setting can be configured at server level properties

And using exec dbo.sp_configure

19: How can I specify the Max Degree of Parallelism at the query level?

Use a MAXDOP hint to override the default server MAXDOP setting

CREATE NONCLUSTERED INDEX [IX_Address_StateID]

 ON [Person].[Address] ([StateID] ASC)

 WITH (MAXDOP=0)

 GO

20: True or False – It is possible to correlate the Performance Monitor metrics with Profiler data in a single SQL Server native product?

True – This functionality is possible with SQL Server Profiler.

21: What is a query plan and what is the value from a performance tuning perspective?

A query plan is the physical break down of the code being passed to the SQL Server optimizer. The value from a performance tuning perspective is that each component of the query can be understood and the percentage of resource utilization can be determined at a micro level.  As query tuning is being conducted, the detailed metrics can be reviewed to compare the individual coding techniques to determine the best alternative.

22: True or False – It is always beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores.

False, this is just a myth.

23: Explain the NOLOCK optimizer hint and some pros\cons of using the hint?

The NOLOCK query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction allowing the query to complete without having to wait for the first transaction to finish and therefore release the locks. This is one short term fix to help prevent locking, blocking or deadlocks. However, when the NOLOCK hint is used, dirty data is read which can compromise the results returned to the user.

24: Can you please name different approaches to capture a query plan.

• SHOWPLAN_TEXT
• SHOWPLAN_ALL
• Graphical Query Plan
• dm_exec_query_optimizer_info
• dm_exec_query_plan

25: Name three different options to capture the input (code) for a query in SQL Server.

• DBCC INPUTBUFFER
• fn_get_sql
• dm_exec_sql_text

26: Explain a SQL Server deadlock

A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the dead lock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting in a performance issue.

27: How a deadlock can be identified?

Deadlocks can be identified by Profiler in either textual, graphical or XML format.

28: How deadlock is a performance problem and some techniques to correct deadlocks?

Majorly Deadlocks are Database Design problem but they are a performance problem as well because they can prevent 2 or more processes from being able to process data.  A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc. Deadlocks could require a database design change, T-SQL coding change to access the objects in the same order, separating reporting and OLTP applications, including NOLOCK statements in SELECT queries that can accept dirty data, etc.

29: Please explain why SQL Server does not select the same query plan every time for the same code (with different parameters) and

The query plan is chosen based on the parameters and code being issued to the SQL Server optimizer.  Unfortunately, a slightly different query plan can cause the query to execute much longer and use more resources than another query with exactly the same code and only parameter differences.

30: How SQL Server can be forced to use a specific query plan.

The OPTIMIZE FOR hint can be used to specify what parameter value we want SQL Server to use when creating the execution plan.