Sql Server Profiler & Database Engine Tuning Adviser

Sql Server Profiler & Database Engine Tuning Adviser  

Step 1: Write a Query

(I have collected this from Microsoft web)

Important DBCC Commands & SP's

DBCC Commands and Stored procedures 

DBCC SHRINKDATABASE(database name)--Shrinks the database files--KEEP IN MASTER DATABASE
DBCC SHRINKFILE(databasename)--
DBCC DBREINDEX--fragmentation(
DBCC CHECKIDENT--(Checking identity)
DBCC SHOWCONTIG--
DBCC CHECKDB----
DBCC SHOWCONTIG WITH TABLERESULTS
DBCC INPUTBUFFER( )  --we get code from window

-- Clear the data and plan cache

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

SP_WHO2
SP_help
SP_Helptext 'table name | procedure name'

sp_configure 'show advanced options',1
reconfigure
Note: 0 will de-activates the configure value
          1 will activates the value
Here Config_Value is 0. So we have 17 Records only

Step 2: Here Config_Value is 1. So we have more number of records

Step 3: Now i need to enable xp_cmdshell (take a look on above image last record . The config_value and run_value are 0 means inactive)

run the reconfigure

How to send a mail when CUP_BUSY is grater than 80

How to send a mail when CUP_BUSY is more than 80

if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.

        begin

                raiserror(15247,-1,-1)

        end

/*

**  Declare variables to be used to hold current monitor values.

*/

declare @now datetime

declare @cpu_busy int

declare @io_busy int

declare @idle int

declare @pack_received int

declare @pack_sent int

declare @pack_errors int

declare @connections int

declare @total_read int

declare @total_write int

declare @total_errors int



declare @oldcpu_busy int /* used to see if DataServer has been rebooted */

declare @interval int

declare @mspertick int /* milliseconds per tick */



/*

**  If we're in a transaction, disallow this since it might make recovery

**  impossible.

*/

set implicit_transactions off

if @@trancount > 0

begin

raiserror(15002,-1,-1,'sp_monitor')

end
/*

**  Set @mspertick.  This is just used to make the numbers easier to handle

**  and avoid overflow.

*/

select @mspertick = convert(int, @@timeticks / 1000.0)



/*

**  Get current monitor values.

*/

select

@now = getdate(),

@cpu_busy = @@cpu_busy,

@io_busy = @@io_busy,

@idle = @@idle,

@pack_received = @@pack_received,

@pack_sent = @@pack_sent,

@connections = @@connections,

@pack_errors = @@packet_errors,

@total_read = @@total_read,

@total_write = @@total_write,

@total_errors = @@total_errors



/*

**  Check to see if DataServer has been rebooted.  If it has then the

**  value of @@cpu_busy will be less than the value of spt_monitor.cpu_busy.

**  If it has update spt_monitor.

*/

select @oldcpu_busy = cpu_busy

from master.dbo.spt_monitor

if @oldcpu_busy > @cpu_busy

begin

update master.dbo.spt_monitor

set

lastrun = @now,

cpu_busy = @cpu_busy,

io_busy = @io_busy,

idle = @idle,

pack_received = @pack_received,

pack_sent = @pack_sent,

connections = @connections,

pack_errors = @pack_errors,

total_read = @total_read,

total_write = @total_write,

total_errors = @total_errors

end



/*

**  Now print out old and new monitor values.

*/

set nocount on

select @interval = datediff(ss, lastrun, @now)

from master.dbo.spt_monitor

/* To prevent a divide by zero error when run for the first

** time after boot up

*/

if @interval = 0

select @interval = 1

--select last_run = lastrun, current_run = @now, seconds = @interval

-- from master.dbo.spt_monitor



select

@cpu_busy =  convert(varchar(11), convert(int, ((((@cpu_busy - cpu_busy)

* @mspertick) / 1000) * 100) / @interval))

from master.dbo.spt_monitor
  IF(@cpu_busy>80)
  BEGIN
msdb.dbo.SP_SEND_DBMAIL    @profile_name=suresh
,   @recipients='',   @subject='', @body=''

  END

SP_EXECUTESQL in SQLSERVER

EXEC SP_EXECUTESQL

SET NOCOUNT ON
GO
CREATE PROC usp_matrimony(@CNTRYNAME VARCHAR(30),@STNAME VARCHAR(30)=NULL,@LASSS INT=NULL,@LASDTRANS VARCHAR(10)=NULL)
AS
BEGIN
DECLARE @QRY NVARCHAR(MAX)=''
SET @QRY='select TOP 10 PERCENT C.Name AS CNAME,C.CountyID,CityID,T.StateID,T.Name AS SNAME,T.LSAD,T.LSAD_TRANS from County C 
INNER JOIN City T ON C.StateID=T.StateID WHERE C.Name=@CNTNAME'

IF(@STNAME IS NOT NULL)
SET @QRY=@QRY+' AND T.Name=@SNAME '

IF(@LASSS IS NOT NULL)
SET @QRY=@QRY+' AND T.LSAD=@LASSS '

IF(@LASDTRANS IS NOT NULL)
SET @QRY=@QRY+' AND T.LSAD_TRANS=@LASDTRANS'
 
EXEC SP_EXECUTESQL @QRY,N'@CNTNAME VARCHAR(30),@SNAME varchar(30)',@CNTNAME=@CNTRYNAME,@SNAME=@STNAME
 
END


EXEC usp_matrimony 'Mobile','lester'

SIMPLE SSAS CUBE and DIMENSIONS

SIMPLE SSAS CUBE with default  DIMENSIONS created

Step 1: Open MSBI-->GO TO file-->select NEW PROJECT-->select ANALYSIS SERVICE as shown in figure-->NAME it as your choice (my self it is MySimpleCube)-->then click OK

then the wizard will be like this

Step 2:
Right click on data Sources -->select new (see below image)

Step 3: Drop down at provider select micro soft OLEDB Provider for sql server --> then OK(See below image)

Next select Server name -->Select Data base name-->Test connection --> click OK

Step 4: Select use the service account--> click NEXT

Step 5: Then click FINISH

 

Step 6: Right click on data source view --it shows the data source view wizard--> Click Next

Step 7: Select Use the service account--> click next

 Step 8: Select Any one of the table then click on Add related tables--> then click next

Step 9: Then Click on Finish

Step 10: Now the wizard will appear like below

 

Step 11:Now right click on cube--> select New Cube-->click next -->select use existing tables-->click Next

 Step 12: Directly click on Suggest (Default it will select the tables) --> click Next

Step 13: click Next

Step 14: Again Click next

Step 15: Before going to finish observe there is no underlying cubes or dimensions respectively-->next click finish

Step 16: now you will see the cube and dimensions generated automatically

Step 17: now Click on Process(Rounded symbol with red mark)--> then click Yes

Step 18:Click on Run

Step 19: After finishing process progress click on close

Step 20: Now click on Browser(Marked with red color)

 

Step 21: Drag and drop the measures at middle,Except that remaining you can drag and drop on Row filters and column filters and filter fields

 Finally solution will be like this (Observe above image)

CTE & RECURSIVE CTE(Common Table Expression)

Common Table Expression

Simple CTE

Print 1 to N numbers

;
WITH CTE AS
(
    SELECT NoM=1
    UNION ALL
    SELECT NoM+1 FROM CTE WHERE NOM+1<=10
) SELECT * FROM CTE

Ans:

Find the Employees under manager 

;WITH X AS
(
    SELECT * FROM EMP WHERE mgr=7788
    UNION ALL
    SELECT e.* FROM x  INNER JOIN emp e ON e.empno=x.mgr
)
SELECT * FROM X

Ans:

Find Cumulative Salary of employees 

select SAL,(select SUM(sal) from emp e where e.empno<=a.empno)as CUM_SAL from emp a

FUNCTION TO concatenate all COLUMNS OF a TABLE

FUNCTION TO concatenate all COLUMNS OF a TABLE 

USE AdventureWorksDW2008
GO

CREATE FUNCTION fn_columns( @tblname varchar(50))
returns varchar(MAX)
as
BEGIN
DECLARE @name VARCHAR(500)=''
SELECT @name=name+', '+@name FROM SYS.columns WHERE OBJECT_ID=OBJECT_ID(@tblname)
RETURN left(@NAME,len(@NAME)-1)
END

 SELECT NAME,dbo.fn_columns(name)as COLUMNS from sys.tables