Pages

Wednesday, January 28, 2015

Change SQL Server user login account password

Change SQL Server user login account password script

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'

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

Monday, January 12, 2015

Find Most Expensive Queries Using DMV


Find Most Expensive Queries Using DMV


Following is the query which finds out top 10 most expensive queries

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)  qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Source : http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/