Please complete in SQL Server Express (free). The objective of this project is t

Question

Please complete in SQL Server Express (free). The objective of this project is to practice using Windows utilities and SQL Server management
tools to monitor performance and diagnose query performance.
1. 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/database. Document this step by taking a screen shot of the real-time
display.
2. 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.
a. Identify the most costly plan steps.
b. Are any of the steps that you identified in Step 2(a) a cause for concern? Explain.
c. Describe how the performance of the query could be improved, or explain why the
query is already optimal.
3. Implement any changes identified in 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.
4. 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.
5. Launch the Database Engine Tuning Advisor from Query Editor to analyze

DATA FOR THE TABLES YOU MUST CREATE:

. Using Query Editor, construct and execute a SQL command to create a new table named Address with the following column names, associated data types, and constraints Column name AddressID Street City State ZipCode nvarchar(50) nvarchar(50) char 2) nvarchar10) Constraint Primary key Not mull Not mull Not mull Not mull

Explanation / Answer

As per given data SQL statements:

This following explanation for determine the number of sessions which are are connected to any database server that is SQL server.

Following procedure for viewing the DB connections which are currently connected to DB:

*SQL select statement*

SELECT

DataBase_NAME(dbid) as DBName,

COUNT(dbid) as ConnectionsCount,

loginame as LoginName

FROM

sys.sysprocesses

*where condition for which is grater then bdid*

WHERE

dbid > 0

*grouping statements through bbid,loginame by using GOUP BY statement*

GROUP BY

dbid, loginame

*this statement gives toatal *

SELECT

COUNT(dbid) as TotalConnections

FROM

sys.sysprocesses

WHERE

dbid > 0

To performing all the above statements in SQL server for count of number of connections we need to use ‘sysadmin’ role , if we are not using it will shows single row and a count of only one connection as the result.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.