Home
> Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 208 : SQL Server DBA Scripts : Identifying Procedures Taking More Execution time
BI SQL # 208 : SQL Server DBA Scripts : Identifying Procedures Taking More Execution time
Hi Folks,
In this article we are going to cover How Identifying Procedures Taking More Execution time.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
How to Identify the Procedures in database which are taking More Execution time ?
Description of SQL Script:
This script will help to identify Procedures in database which are taking More Execution time.
SQL Script Output Column
SQL Script Code
SELECT TOP (25) p.NAME AS [SP Name] ,qs.total_physical_reads AS [TotalPhysicalReads] ,qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads] ,qs.execution_count ,ISNULL(qs.execution_count / DATEDIFF(Second, qs. cached_time, GETDATE()), 0) AS [Calls/Second] ,qs.total_elapsed_time ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs. [object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_physical_reads DESC;
SQL Script Output Screenshot
User Level to execute
300
Hope you will like How Identifying Procedures Taking More Execution time.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
Advertisement
Categories: Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar
Tags: DBA, Fast SQL, Microsoft SQL Server, SQL Help, SQL Mentalist, SQL Script, SQL Server, SQL Server 2008 R2, SQL Server 2012, SQL tips, SQL Tricks, TSQL, Vishal Pawar
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
Search Here !!
Join 2,993 other subscribers
Mentalist Blogs
- Blog on Beyond Relational's
- Business Intelligence Mentalist BI World
- Link Resource Blog Daily Intersting links
- Microsoft Mentalist MVC,WCF
My Recent Post
- My session in SQL Saturday #859 Tampa – Learn Power BI Report Server Enterprise Architecture, Tools to publish reports and Best Practice
- BI SQL # 281 : All Power BI Dashboard Query consolidated & Information
- SQLSaturday #699 Tampa – Power BI-Create a Dashboard from Zero to Hero
- Product Review – Stellar Phoenix SQL Database Repair
- BI SQL # 280: Get all SQL Server instance information for at a glance
- BI SQL # 279 : Get all last back up date and time with physical bak file path
- Image Post–SQL 2016, Azure and Everything around it !
- BI SQL # 278 : SQL Server DBA Scripts : How to Quick Search Anything in Database in SQL Server
- 2015 in review–BI,SQL Blog and Power BI (About me) , Happy New Year 2016
- Merry Christmas & Gifts of Slides on SQL Coding Conventions, Best Practices, Tips and Programming Guidelines for TSQL
- SQL Server 2016–Getting Started, Download, Installation and First Look
- Back to origin, SQL Saturday Atlanta event day updates – SQLSaturday #392
- 2014 in review ! Wish you all Happy New Year ! Happy SQL Scripting !
- BI SQL # 277 : SQL Server DBA Scripts : Find TOP IO Stored Procedure
- BI SQL # 276 : SQL Server DBA Scripts : Find all DB IO with Rank
- BI SQL # 275 : SQL Server DBA Scripts : Find Session details
- BI SQL # 274 : SQL Server DBA Scripts : Space used by version store
- BI SQL # 269 : SQL Server DBA Scripts : Find all server running in n/w
- BI SQL # 273 : SQL Server DBA Scripts : Core in server
- BI SQL # 272 : SQL Server DBA Scripts : Find Connectivity information
- Microsoft Power Business Intelligence (Power BI) Workshop in Microsoft Mumbai Office
- BI SQL # 271 : SQL Server DBA Scripts : Display all Server Information
- BI SQL # 270 : SQL Server DBA Scripts : Find the SQL Server Protocols Status
- BI SQL # 268 : SQL Server DBA Scripts : Find Statistics Info for all tables
- BISQL – Laymen to SQL Developer # 45 – Security & Integrity #2 – Authorization, Granting of Privileges, Security Specification in SQL
- BI SQL # 267 : SQL Server DBA Scripts : Identity Columns: Are You Nearing The Limits?
- BI SQL # 266 : SQL Server DBA Scripts : Find Cumulative IO Stats
- BI SQL # 265 : SQL Server DBA Scripts : Compare Data Table
- BISQL – Laymen to SQL Developer # 44 – Security & Integrity #1 – Introduction, Security & Integrity Violations
- BI SQL # 264 : SQL Server DBA Scripts : Monitoring Database Backups
- BI SQL # 263 : SQL Server DBA Scripts : All Database Information
- BI SQL # 262 : SQL Server DBA Scripts : Find IP Address of machine
- BI SQL # 261 : SQL Server DBA Scripts : Find If any database is Offline
- BISQL – Laymen to SQL Developer # 43 – Relational Databases #2 – Object Oriented Data Model, Object Oriented Databases, Object Relational Database Management Systems
- BI SQL # 260 : SQL Server DBA Scripts : Find last backup details of all databases in a server
- BI SQL # 259 : SQL Server DBA Scripts : Database current size, auto growth setting and file location
- BI SQL # 258 : SQL Server DBA Scripts : Index Definition Audit Script
- BISQL – Laymen to SQL Developer # 42 – Relational Databases #1 – Introduction, Basics of Object Oriented Design (OOD)
- BI SQL # 257 : SQL Server DBA Scripts : SQL Agent Job Script
- BI SQL # 256 : SQL Server DBA Scripts : Check VLF Counts in Database
- BI SQL # 255 : SQL Server DBA Scripts : Find Recently Executed Stored Procedures
- BI SQL # 254 : SQL Server DBA Scripts : Estimating Rows per Page
- BISQL – Laymen to SQL Developer # 38 – Recovery Techniques #1 – Introduction, Techniques for Recovering
- BI SQL # 253 : SQL Server DBA Scripts : Permissions Script
- BI SQL # 252 : SQL Server DBA Scripts : Find SQL Server Start time and End time
- BI SQL # 251 : SQL Server DBA Scripts : List SQL Script to find the Roles assigned to the SQL Logins including Create_data and modify_date
- BISQL – Laymen to SQL Developer # 41 – Distributed Databases #2 – Data Fragmentation, Replication, and Allocation Techniques for Distributed Database Design
- BI SQL # 250 : SQL Server DBA Scripts : List all roles of the current database with their members
- BI SQL # 249 : SQL Server DBA Scripts : Finding tempdb Size and Growth Parameters
- BI SQL # 248 : SQL Server DBA Scripts : Find Tables with Foreign Key Constraint in Database
- BI SQL # 247 : SQL Server DBA Scripts : List all the Databases on Server
- BISQL – Laymen to SQL Developer # 40 – Distributed Databases #1 – Introduction, Client-Server Model
- BI SQL # 246 : SQL Server DBA Scripts : SQL DMV to find the details of current processes running like percent completed!!
- BI SQL # 245 : SQL Server DBA Scripts : Show Running Queries on SSMS
- BI SQL # 244 : SQL Server DBA Scripts : Restore Database Through script
- BISQL – Laymen to SQL Developer # 39 – Recovery Techniques #2 – Deferred Update, Immediate Update
- BI SQL # 243 : SQL Server DBA Scripts : Finding all the objects that have been modified in the last N days in database
- BI SQL # 242 : SQL Server DBA Scripts : Shrink Log file through Script
- BI SQL # 241 : SQL Server DBA Scripts : Find SQL Server Service Account
- BI SQL # 240 : SQL Server DBA Scripts : Foreign Key & Primary Key Relationship All
- BI SQL # 239 : SQL Server DBA Scripts : C. Compare Database
- BI SQL # 238 : SQL Server DBA Scripts : B. Compare Database
- BI SQL # 237 : SQL Server DBA Scripts : A. Compare Database
- BISQL – Laymen to SQL Developer # 37 – Concurrency Control Techniques #2 – The Two Phase Locking Protocol
- BI SQL # 236 : SQL Server DBA Scripts : Find list of stored procedure with content info
- BI SQL # 235 : SQL Server DBA Scripts : Identify Structure of a Table
- BI SQL # 234 : SQL Server DBA Scripts : Get Distinct Value for each column from Table
- BI SQL # 233 : SQL Server DBA Scripts : Trim all column of given table
- BISQL – Laymen to SQL Developer # 36 – Concurrency Control Techniques #1 – Introduction, Types of Locks: Locking Technique for Concurrency Control
- BI SQL # 232 : SQL Server DBA Scripts : Backup Old And Restore new Database through Script
- BI SQL # 231 : SQL Server DBA Scripts : Create Primary Key for all Tables
- BI SQL # 230 : SQL Server DBA Scripts : Disable Data Compression
- BISQL – Laymen to SQL Developer # 35 – Transaction Processing #2 – Desirable Properties of Transactions, Concepts of Schedule History, recoverable cascading rollback and script schedules
- BI SQL # 229 : SQL Server DBA Scripts : Enable Data Compression
- BI SQL # 228 : SQL Server DBA Scripts : Create Alter Script Dynamically
- BI SQL # 227 : SQL Server DBA Scripts : Display Table info with Index and Rows
- BI SQL # 226 : SQL Server DBA Scripts : Display Name And Size of Drive on Machine
- BISQL – Laymen to SQL Developer # 34 – Transaction Processing #1 – Introduction, Transaction Processing Concepts, Transactions in a Multiuser System
- BI SQL # 225 : SQL Server DBA Scripts : Display Space Details for Given database
- BI SQL # 224 : SQL Server DBA Scripts : Display Memory Configuration
- BI SQL # 223 : SQL Server DBA Scripts : Get All table Size Information
- BI SQL # 222 : SQL Server DBA Scripts : Find Tables without Primary Key
- BISQL – Laymen to SQL Developer # 33 – Functional Dependencies and Normalization For Relational Databases #4 – Fourth Normal Form (4NF), Normalization using Join Dependencies
- BI SQL # 221 : SQL Server DBA Scripts : Find Specific text in database
- BI SQL # 220 : SQL Server DBA Scripts : Find Any Column with Specific DataType
- BI SQL # 219 : SQL Server DBA Scripts : Add mdf file as new DB
- BISQL – Laymen to SQL Developer # 32 – Functional Dependencies and Normalization For Relational Databases #3 – Boyce Codd Normal Form (BCNF)
- BI SQL # 218 : SQL Server DBA Scripts : Identify size of all non clustered indexes
- BI SQL # 217 : SQL Server DBA Scripts : Display Configuration Settings
- BI SQL # 216 : SQL Server DBA Scripts : Searching For Specific column for all Tables in database
- BI SQL # 215 : SQL Server DBA Scripts : Making IDENTITY column to NON-IDENTITY & vice versa
- BISQL – Laymen to SQL Developer # 31 – Functional Dependencies and Normalization For Relational Databases #2 – Levels of Relation Schema & Functional Dependency, Normal Forms Based on Primary Keys
- BI SQL # 214 : SQL Server DBA Scripts : Identify Number of Lines of code for each database Object
- BI SQL # 213 : SQL Server DBA Scripts : Reads and Writes in Database
- BI SQL # 212 : SQL Server DBA Scripts : Searching String in Steps of JOB
- BISQL – Laymen to SQL Developer # 30 – Functional Dependencies and Normalization For Relational Databases #1 – Introduction, Information Design Guide Lines for Relational Database
- BI SQL # 211 : SQL Server DBA Scripts : How to Drop All Table from database through Script
- BI SQL # 210 : SQL Server DBA Scripts : Reset Identity Column
- BI SQL # 209 : SQL Server DBA Scripts : Displaying Table Name Having Primary Key with Column Name
- BI SQL # 208 : SQL Server DBA Scripts : Identifying Procedures Taking More Execution time
- BI SQL # 207 : SQL Server DBA Scripts : Identify Free space in Temp DB
- BI SQL # 206 : SQL Server DBA Scripts : Identify IO Stall For Each DB
- BI SQL # 205 : SQL Server DBA Scripts : Identify High Latch Waits
- BISQL – Laymen to SQL Developer # 29 – SQL #3 – The Create Table Statement, Specifying of Constraints & Default Values of SQL
- BI SQL # 204 : SQL Server DBA Scripts : Identify Top Nth waits With I / O Specification
- BI SQL # 203 : SQL Server DBA Scripts : Identify Top Nth heavy I / O Operation
- BI SQL # 202 : SQL Server DBA Scripts : How to Delete Database through Script
- BI SQL # 201 : SQL Server DBA Scripts : Job Failure Notification
- BISQL – Laymen to SQL Developer # 28 – SQL #2 – Multi Table Queries, Data Manipulation Language
- BI SQL # 200 : SQL Server DBA Scripts : Various SQL Server IO Statistics
- BI SQL # 199 : SQL Server DBA Scripts : Temporary Tables and Their Size
- BI SQL # 198 : SQL Server DBA Scripts : Stored procedure Execution statistics
- BISQL – Laymen to SQL Developer # 27 – SQL#1 – Introduction, Tuple Relational Calculus
- BI SQL # 197 : SQL Server DBA Scripts : Overview of all objects created in SSRS ordered by the folder hierarchy
- BI SQL # 197 : SQL Server DBA Scripts : Overview of all objects created in SSRS ordered by the folder hierarchy
- BI SQL # 196 : SQL Server DBA Scripts : Script to VLF count for all databases
- BI SQL # 195 : SQL Server DBA Scripts : SQL Server replication publication information
- BISQL – Laymen to SQL Developer # 26 – Relational Algebra #2 – Update Operation on Relations, The Relational Algebra
- BI SQL # 192 : SQL Server DBA Scripts : Search string or pattern in SQL module definition
- BI SQL # 191 : SQL Server DBA Scripts : Search for a string in all tables of SQL Server Database
- BISQL – Laymen to SQL Developer # 25 – Relational Algebra #1 – Introduction, Relational Model Constraints
- BI SQL # 190 : SQL Server DBA Scripts : Script to find the partition key of a table
- BI SQL # 189 : SQL Server DBA Scripts : Run DBCC CHECKDB on all databases
- BI SQL # 187 : SQL Server DBA Scripts : Query Master Data with Details as Comma Separated Value
- BISQL – Laymen to SQL Developer # 24 – Entity-Relationship Model #3 – Components of an ER Model, Constraints on Relationships Types
- BI SQL # 186 : SQL Server DBA Scripts : Find the details of the given report name by querying Report Server Database
- BI SQL # 185 : SQL Server DBA Scripts : Programmability Objects and their Parameters
- BI SQL # 184 : SQL Server DBA Scripts : Pending IO Requests
- BISQL – Laymen to SQL Developer # 23 – Entity-Relationship Model #2 – ER Model Concept with an example
- BI SQL # 183 : SQL Server DBA Scripts : Pages per object in buffer pool
- BI SQL # 182 : SQL Server DBA Scripts : Page Life Expectancy
- BI SQL # 181 : SQL Server DBA Scripts : Overview of Locks per Database and Type
- BI SQL # 180 : SQL Server DBA Scripts : List log file information for all the databases on the server
- BISQL – Laymen to SQL Developer # 22 – Entity-Relationship Model #1 – Introduction, Conceptual Data model for Database Design
- BI SQL # 179 : SQL Server DBA Scripts : Log Growth Rate
- BI SQL # 178 : SQL Server DBA Scripts : List expensive queries MAIN
- BI SQL # 177 : SQL Server DBA Scripts : List expensive queries CHILD
- BISQL – Laymen to SQL Developer # 21 – Index Structures of Files #2 – Multilevel Indexes, B+Tree Index Files, B-Tree’
- BI SQL # 176 : SQL Server DBA Scripts : List for all SSRS reports that use data sources and datasets with the command text
- BI SQL # 175 : SQL Server DBA Scripts : List Database Roles and Members with Server Login
- BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources
- BI SQL # 173 : SQL Server DBA Scripts : List all trigger with SQL definition
- BISQL – Laymen to SQL Developer # 20 – Index Structures of Files #1 – Introduction, Primary Index, Clustering Index
- BI SQL # 172 : SQL Server DBA Scripts : List all SSRS subscriptions
- BI SQL # 171 : SQL Server DBA Scripts : List all SSRS report parameters with definition
- BI SQL # 170 : SQL Server DBA Scripts : List all SSIS packages stored in msdb database
- BISQL – Laymen to SQL Developer # 19 – Record Storage and Primary File Organization #4 – Hashing Techniques
- BI SQL # 169 : SQL Server DBA Scripts : List all Server Permissions
- BI SQL # 168 : SQL Server DBA Scripts : List all Objects and Indexes per Filegroup or Partition
- BI SQL # 167 : SQL Server DBA Scripts : List all Locks of the Current Database
Categories
- A to Z SQL Command (9)
- Free Tool (8)
- Link (248)
- Microsoft SQL Server (204)
- MSBI (205)
- New blog (5)
- NMUG (3)
- Optimization (204)
- PASS (3)
- Query (270)
- Query, (4)
- Script (200)
- SQL (148)
- SQL Mentalist (209)
- SQL PraRup (193)
- SQL Query (204)
- SQL Server (199)
- SQL Server 2014 (6)
- SQL Server 2016 (7)
- SQL Tips (15)
- SQL Tips and Tricks (17)
- SQL Tricks (41)
- Technology (6)
- Technology, (203)
- TSQL (176)
- Vishal Pawar (214)
- ZeroToBasic (15)
SQL Server Mentalist 