CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Best way to determine issue with stored procedure performance?

Strored Procedure showing high number of reads in sql server profiler .

CPU of 335886 Reads of 261841162 Writes 7868 Duration 340510

This only happens with a certain account.

What is my 1st steps in determining the problem here ?

Stored Procedure tuning advice required ...

Upvotes: 0

Views: 5106

Answers (1)

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16134

Try Database Engine Tuning Advisor

  1. Create a trace file using Sql Profiler
  2. Use it a workload in Database Engine Tuning Advisor.
  3. Try implementing the suggestion given by Database Engine Tuning Advisor.

After analyzing a workload, Database Engine Tuning Advisor can recommend that you add, remove, or modify physical design structures in your databases. The advisor can also recommend what statistics should be collected to back up physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning. Database Engine Tuning Advisor recommends a set of physical design structures that reduces the query optimizers estimated cost of the workload.

Refer this:

Database Engine Tuning Advisor Overview

Tutorial: Database Engine Tuning Advisor

Upvotes: 1

Related Questions