• DBA,  Performance

    Integrate SSRS with Perfmon – Dashboard performance of database graph using SSRS Collect perfMon and automate it load to SQL database and generate reports

    DBA wants to collect metrics and analysis them and present them to management to get something done by using the data and it is all about data and metric nowadays. How to collect them and show as a presentation. Tools needed: SSRS, Perfmon.msc, Relog.Exe, database engine, power shell & CMD. Configure SSRS Installing SSRS If you have not installed SSRS, install the service first and configure it. Go to the software folder and double click the setup – click installation in the left side –New SQL server standalone or add features on existing installation – Next and next – select “Add features to an existing instance of SQL server Next…

  • DBA,  Performance

    Use of SQL server statistics and how to make a query to use them

    Statistics are very important in the query optimization. This post will give you some basic idea of stats and how they are created and used. This post is to link with my performance tuning parts. What is statistics: Statistics store information about the distribution of data in the column/columns for a table. It has Header, Densities and Histogram. The Histogram stores only 200 steps, and for very large tables VLT, it is hard to hold much data only can hold up-to 200 steps, you have to create manually for the VLT. It is a lightweight and use very low storage. The only way to know the underlying tables from the…

  • DBA,  Performance

    Performance Tuning Steps Query by looking an execution plan and effectiveness of the index Part 2

    How to read execution plan to improve poorly performing queries Execution plan is optimizer’s calculation of how the query will be executed by creating the best low-cost query plan (if it is already in the cache, it will reuse).  SQL server is cost based optimizer.  Meaning, the optimizer decides based on CPU, I/O and statistics etc and creates a plan and returns the results. Up to date statistics is very important one to create a more accurate plan. We can view this in GUI and text both will be helpful, when the scenario come in.   Types:  Estimated and Actual execution plan – both will be helpful. Estimated plan: It…

  • DBA,  Performance

    Performance Tuning Steps a Query by looking an execution plan and effectiveness of the index creation Part-1

    The index is very important and powerful object in the database. It is an optional and secondary object for the table. It will speed up the data return time by navigating the B-tree structure, like a book’s index. In General, index tuning is important one, putting a right index will speed up the code.  Please read this post for finding symptoms, where you have issue at server or database or query level.  Most of the time, the query will be the issue. Once, you have long running query start tuning it. In this post, I am going to share the usage and effectiveness of index from low level to high…

  • DBA,  Performance

    Reading execution plan sql server use of Plan Explorer

    Plan Explorer I recently started using this. Since it was licensed earlier, now it’s free. This is an awesome tool for query performance tuning. SQL Server Sentry Plan Explorer is the free tool given to SQL community you can download here https://www.sentryone.com/plan-explorer click download plan explorer button with right bit. It will not ask you much for download. Plan Explorer really makes DBAs job easier, this will help to read the bigger execution plans quickly and which code block  taken more time duration, even if it’s 1000 lines code. Once you have installed, open the tool click file –> new and on command text ” select * from sysobjects o…