SQL Server

Index fragmentation query – sql server

what is the level of fragmentation on my indexes?

SELECT TOP 50 s.NAME SchemaName
,t.name TABLENAME
,i.name AS TableIndexName
,i.[type_desc]
,p.avg_fragmentation_in_percent
,i.[fill_factor]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) p --'DETAILED'
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id

WHERE p.avg_fragmentation_in_percent > 1
AND s.NAME ='mySchema'
ORDER BY avg_fragmentation_in_percent desc

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments

Delete records in a batch


WHILE 1 = 1
BEGIN
DELETE TOP(50000) [mySchema].[DimStatic] WITH (XLOCK)
WHERE [PKDimStaticID] NOT IN (SELECT [FKDimStaticID] FROM [#tt])
IF @@rowcount < 1 BREAK; END

Tags: , ,

Friday, March 2nd, 2012 SQL Server No Comments

Delete Dupes using CTE

for my own reference:
delete dupe records, keeping one entry for each

;WITH mycte (PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,myrank)
AS
(
SELECT
PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,
RANK() OVER (PARTITION BY PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId
ORDER BY PKFACTMarketDataId) AS myrank
FROM DCRMCube.FACTMarketData
)

DELETE FROM mycte
WHERE MYRANK >1

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments

Table Row counts Sql Server 2005

You can get the same in SSMS, by selecting ‘Reports’ option in the Database context sensitive menu

SELECT 
	distinct Table_Name = object_name(object_id), 
	T.TABLE_SCHEMA,
	Total_Rows =  st.row_count
FROM sys.dm_db_partition_stats st INNER JOIN information_schema.tables T
on object_name(object_id) = T.TABLE_NAME
where object_name(object_id) not like 'sys%' and object_name(object_id) not like 'queue%'
ORDER BY object_name(object_id) 
Wednesday, January 7th, 2009 SQL Server No Comments

sqlcmd & dtexec params and syntax

SQLCMD
sqlcmd -S MySERVER -E -d master -i APPLY_DB_SECURITY.sql -o LOG\APPLY_DB_SECURITY.log

DTEXEC
dtexec /FILE “\\MYSERVER\BI Environments\myfolder\ETL\SSIS\MasterETL\LoadControl.dtsx”
/CONNECTION SSISControl;”\”Data Source=MYSERVER;Initial Catalog=mydb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF
/SET “\Package.Variables[User::RootPath].Properties[Value]”;”\\MYSERVER\BI Environments\mpadigela_dev1\ETL”
/SET “\Package.Variables[User::ServerName].Properties[Value]”;”MYSERVERNAME”
/SET “\Package.Variables[User::EnviromentName].Properties[Value]”;”myEnvironment_”
/REPORTING EWCDI

Tags: ,

Tuesday, December 9th, 2008 SQL Server, SSIS No Comments

Speedup SSMS and Visaul Studio

SSMS

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe”  /nosplash /S MYSERVER /E /D MYDB

so, no splash screen, connects to MYSERVER server,  MYDB database using Integrated Security.

VS

“C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe”  /nosplash

Tags: ,

Tuesday, December 9th, 2008 SQL Server No Comments

T-SQL String Concatenation using FOR XML PATH(”)

Sometimes we need to convert (concatenate) string column values for a given group into a
comma(or any character) separated list of values.

eg:
I have this:

I want this:

here is one way of doing this in T-SQL using FOR XML PATH(”) (only works with SQL Server 2005 and above)

select NoteID, STUFF(
	(SELECT  ',' + a.Comments  AS [text()]
	from Notes  a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH('')),1,1,''	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

or convert all rows into csv..

SELECT 'MP' NAME
into #tt1
UNION
SELECT 'BP' NAME
UNION
SELECT 'HP' NAME

select name from #tt1

declare @strTemp as varchar(100)

Select @strTemp = STUFF(
	(SELECT  ',' + a.Name  AS [text()]
	from #tt1  a
	for xml PATH('')),1,1,'' )  

print @strTemp

STUFF function removes the leading comma

Tags: , ,

Monday, September 29th, 2008 SQL Server No Comments

Date to/from ID Conversion – TSQL

/*Convert DateTime to ID (yyyymmdd)*/
select convert(char(10),getdate(),112)

/*Convert DateID (yyyymmdd) to Date*/
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20080922))

Tags: , , ,

Saturday, September 27th, 2008 SQL Server No Comments

Troubleshooting Performance Problems in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Friday, August 22nd, 2008 SQL Server No Comments

Memory configuration settings in SQL Server

some useful links on SQL Server memory management:

http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html

http://sqlnerd.blogspot.com/2007/07/further-explanation-of-sql-memory-use.html

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

http://support.microsoft.com/?kbid=316739