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.
-
-
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 (to check performance issue, benchmark for migration etc) and it is all about data and metric nowadays. How to collect them and show as a presentation. Two parts are there: 1. You can setup only Perfmon, T-SQL and use excel 2. Setup perfmon, T-SQL and configure SSRS. Tools needed: Perfmon.msc, Relog.Exe, database engine, PowerShell & CMD. Steps: Create two notepad and save it any drive (performance.lst, server.lst). The performance notepad will have counter details and server notepad will have server name Create one CMD file called createlogs.cmd. Createlogs cmd…
-
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…
-
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…
-
None of the IP addresses configured for the availability group listener can be hosted by the server Either configure a public cluster network 19456
Requirement: Remove a node from one of availability alwayson group and added it on another group. It was a multi-subnet and the node is a DR node on a different subnet. You have to remove the IP from the group in the windows cluster go to run — cluadmin.msc — click roles — click the group –> click on server name expand it — remove the IP of the node it could be offline. Once deleted add it on the other group by the listener or from the cluster. Background: It is a three node cluster two in same data center and third node is in DR data center. TITLE:…