• DBA

    SQL server registered server CMS usage

    SQL server registered server usage It is very useful for DBAs and junior DBAs to collect information from all the servers, reports etc. Example: Collect all the logins, jobs, larger database size from all the 300+ servers etc. It is per user, each one needs to create their own category version wise or environment wise. Make sure, the query will run all the servers, try to use select mostly, if you are performing and DDL or DML verify it before running, since it will make changes in all the servers. You can also use centralized management server, it is for all users.

  • DBA,  Performance

    Parameter sniffing problem PSP SQL server database capture and analysis options

    Being a DBA we could see many of PSP parameter sniffing problem is SQL server database. I have seen many of it, following is the way to capture and analysis. Issue 1 – We had issue for one of the important claim data loads every night, which suddenly running long time to load the data. Issue 2 – The website is going unresponsive state daily once or twice By default SQL server will sniffing the parameter, meaning the SQL engine will store the plan in cache for any parameters the same plan will be used in the future. It is good most of the time, since parameter Sniffing is useful…

  • DBA

    How to delete rows in a table are same identical rows in all the fields where clause column

    How to delete rows in a table are same identical rows in all the fields where clause column. Recently, i got this from one of my friend.   create table tbl_delete (id int, name varchar(30)) insert into tbl_delete values (1,'Muthu') insert into tbl_delete values (1,'Muthu') insert into tbl_delete values (2,'SQL') insert into tbl_delete values (2,'SQL') -- Two rows are same with all fileds select * from tbl_delete WHERE id =1 Begin tran SET ROWCOUNT 1 DELETE FROM tbl_delete WHERE id =1 SET ROWCOUNT 0 -- Run this when you see (1 row(s) affected), otherwise rollback tran Commit tran Hope this help someone.  

  • DBA,  Performance

    Perfmon Data collection – 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…