Using the Windows Performance Monitor utility, add a set of counters to the real-time performance monitor display that you consider most relevant to the performance of your SQL Server instance. Document this step by taking a screen shot of the real-time display.
Windows Performance Monitor is used to study how programs running in your laptop or desktop affect computer's performance. The performance is monitored both in real time and by collecting log data for later examination.
In SQL Server Management Studio, construct a SQL query that uses a SELECT statement and a JOIN clause to query data from both the Customer and Address tables. Analyze the estimated execution plan.
Identify the most costly plan steps.
JOIN Clause is the very costly when it comes to windows performance. At the start of the Join Query, the memory level was at 30 but at the end of running the join query, the memory had reached the 40 level mark.
Are any of the steps that you identified in Step 2(a) a cause for concern? If so, explain.
Yes, a Left Join is categorically not faster than an inner join. As a matter of fact, it is slower, a left join, right join or inner join has to do all the job of an inner join and the extra work for null-extending the results. It is also anticipated to return more rows, increasing the total execution time because of the larger size of the result set.
Describe how the performance of the query could be improved, or explain why the query is already optimal.
The order-by, group-by, and distinct operations are all kinds of sorting in SQL. The SQL Server query processor uses sorting in two techniques. If records in a table are already sorted by an index order, the processor needs to use only the index. Else, the processor has to implement a short-term work table to first sort the records. Such initial sorting causes significant initial delays on computers with lower processing power CPUs and narrow the computer memory, and this should be avoided if response time is vital.
Implement any changes identified in Step 2(c) and execute the query using the option Include Actual Execution Plan. Document this step by taking a screen shot of the actual execution plan.
Using INNER JOIN and avoiding ORDER BY Customer.CustomerName clause improves the speed of execution and reduces cost of I/O.
Use SQL Server Profiler to capture a trace and execute the query again. Analyze the trace and determine how many milliseconds the query took to execute. Take a screen shot of the trace that shows the execution of your query to document this step.
The query took 0.01 milliseconds to execute
Launch the Database Engine Tuning Advisor from Query Editor to analyze your query. Did the analysis generate any recommendations? Explain why or why not.
The Database Engine Tuning Advisor generated No recommendation statement.
The reason is because the DTA found that the query had no many indexes and that the indexes in the query were all used during query execution.
Queries and updates in SQL which take more time than anticipated to complete are caused by a range of reasons. Slow queries are affected by performance hitches associated with the network or the computer where that SQL Server Software is installed. Slow-running queries are also initiated by hitches with the physical database design. As we have seen Database Engine Tuning Advisor can analyse your database and recommend proper ways to design your database.
A query optimizer uses indicators to generate query strategies which increase query speed of execution. In most queries, query optimizer provides and creates the essential statistics for a good quality query strategy. In a rare cases, you are required to generate extra statistics to adjust the query for good results.
The Windows Performance Monitor service uses occurrence trace data, configuration information and performance counters that can be shared into data collector Sets. The counters chosen were to diagnose any memory, CPU, or Input / Output issues on my SQL Servers. Another reason but equally important element is reaction time. With effective monitoring, alerting and reporting, we can respond to an emergency before a large portion of computer memory is used or affected especially in devices with low memory space.
After you have recognized the slow-running executing queries, you may further examine a query run time by making a plan that may be an XML, text, or GUI of the query execution plan that the query optimizer produces. You can produce a plan using an SQL management set options, SQL server studio, or SQL server profiler.
