Wednesday, January 28, 2015

Change SQL Server user login account password

Change SQL Server user login account password script:
Sometimes you need to change the password for a particular user in that case following script will help you to change the password.

in following Example :
Login account user name is : dbdevwebuser
Current password is : MyCurrentPwd
New password is : MyNewCurrentPwd

We can use following script to change the users login password.
ALTER LOGIN dbdevwebuser WITH PASSWORD = N'MyNewCurrentPwd' OLD_PASSWORD = 'MyCurrentPwd'
Happy Coding !! 

Friday, January 16, 2015

Reseeding/reset the identity of table.

Generally, on inserting the new data into table we get next seed value for identity column. When a row is deleted, we cannot use that identity value again. We can resolve it by reseeding the identity of table as follows:


1. SELECT * FROM Sample_Data

​Number​Value
​1​First
​2​First
​3​First
​4​First
​5​First
​6​First
​7​First
2. DELETE FROM Sample_Data WHERE Number > 4

​Number​Value
​1​​First
​2​​First
​3​​First
​4​​First
3. DECLARE @MaxID INT
SELECT @MaxID=MAX(Number) FROM Sample_Data
DBCC CHECKIDENT('Sample_Data',RESEED,@MaxID)

GO

RESULT:-
Checking identity information: current identity value '16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4. INSERT INTO Sample_Data(Value) VALUES('Second')
   Go 3

​​Number​​Value
1​​​​First
2​​​​First
3​​​​First
4​​​​First
5​​Second
​6​Second
7​​Second


Happy Coding!!

Monday, January 12, 2015

Find Most Expensive Queries Using DMV


Find Most Expensive Queries Using Dynamic Management Views(DMV)

When you try to find out the performance of an sql server, this query will help us.
It records how many times that execution plan get re-used by following execution. This will give us the count of the execution. It records the number of logical reads, logical writes, elapsed time etc. all this counter will help to find poor performer while monitoring SQL Server queries.
The query will use the Dynamic Management Views. DMV returns server state information that can be used to monitor the health of the server instance, diagnose problems and performance.
Following is the query which finds out top 10 most expensive queries


SELECT TOP 10 SUBSTRING(qt.TEXT, (qstat.statement_start_offset/2)+1,

  ((CASE qstat.statement_end_offset

  WHEN -1 THEN DATALENGTH(qt.TEXT)

  ELSE qstat.statement_end_offset

  END - qstat.statement_start_offset)/2)+1),

  qstat.execution_count,

  qstat.total_logical_reads, qstat.last_logical_reads,

  qstat.total_logical_writes, qstat.last_logical_writes,

  qstat.total_worker_time,

  qstat.last_worker_time,

  qstat.total_elapsed_time/1000000 total_elapsed_time_in_S,

  qstat.last_elapsed_time/1000000 last_elapsed_time_in_S,

  qstat.last_execution_time,

  qplan.query_plan

FROM sys.dm_exec_query_stats  qstat

CROSS APPLY sys.dm_exec_query_plan(qstat.plan_handle) qplan

CROSS APPLY sys.dm_exec_sql_text(qstat.sql_handle)  qt

ORDER BY qstat.total_logical_reads DESC -- logical reads

-- ORDER BY qstat.total_logical_writes DESC -- logical writes

-- ORDER BY qstat.total_worker_time DESC -- CPU time

Happy Coding !!