Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server - SQLTeam.com

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server - SQLTeam.com

SQL DMVStats

SQL DMVStats
A SQL Server 2005 Dynamic Management View Performance Data Warehouse

Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV performance data. DMVstats does not support Microsoft SQL Server 2000 and earlier versions.
The three main components of DMVstats are:
• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

Data collection is managed by SQL Agent jobs. The DMVstats data warehouse is called DMVstatsDB. Analysis and reporting is provided by means of Reporting Services reports.

SQL Nexus Tool

SQL Nexus Tool
SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

Feature Highlights:
Fast, easy data loading: You can quickly and easily load SQL Trace files; T-SQL script output, including SQL DMV queries; and Performance Monitor logs into a SQL Server database for analysis. All three facilities use bulk load APIs to insert data quickly. You can also create your own importer for a custom file type.
Visualize loaded data via reports: Once the data is loaded, you can fire up several different charts and reports to analyze it.
Trace aggregation to show the TOP N most expensive queries (using ReadTrace).
Wait stats analysis for visualizing blocking and other resource contention issues (based on the new SQL 2005 Perf Stats Script).
Full-featured reporting engine: SQL Nexus uses the SQL Server Reporting Services client-side report viewer (it does not require an RS instance). You can create reports for Nexus from either the RS report designer or the Visual Studio report designer. You can also modify the reports that ship with Nexus using either facility. Zoom in/Zoom out to view server performance during a particular time window. Expand/collapse report regions (subreports) for easier navigation of complex data. Export or email reports directly from SQL Nexus. Nexus supports exporting in Excel, PDF, and several other formats.
Extensibility: You can use the existing importers to load the output from any DMV query into a table, and any RS reports you drop in the Reports folder will automatically show up in the reports task pane. If you want, you can even add a new data importer for a new data type. SQL Nexus will automatically “fix up” the database references in your reports to reference the current server and database, and it will provide generic parameter prompting for any parameters your reports support.

SQL DBlog

SQL DBlog

SQL DBlog

SQL DBlog

Using SQL Server 2005 Express from Visual Basic 6

Using SQL Server 2005 Express from Visual Basic 6
Microsoft Desktop Storage Engine (MSDE) has been replaced with a new product called SQL Server 2005 Express, which addresses several limitations of MSDE and can be used with Visual Basic 6.
Click here to download the code sample for this article.

Resources:
T-SQL Enhancements in SQL Server 2005
Using CLR Integration in SQL Server 2005
Sqlexpress's Weblog
SQL Server Developer Center
Visual Basic Developer Center

XPATH QUERY SAMPLE: PIX/ASA ISSUES

-- XPATH QUERY - issues by issue type and day
SELECT ISSUE,STUFF((SELECT ', ' + convert(varchar(10),T2.N) + '('+ convert(varchar(3),y)+')'
FROM pix001_issues AS T2 WHERE T2.issue = T1.issue order by y desc
FOR XML PATH ('')), 1, 2,'') AS N
FROM pix001_issues AS T1
group by issue
order by ISSUE
/*
declare @a varchar(8000)
SELECT @a = COALESCE(@a + ',', '') +
CAST(N AS varchar(50))
FROM pix001_issues where issue= 302013 order by y desc
print @a
*/
-- select * from pix001_issues where issue=105006


Result:
ISSUE N
104004 1(192), 1(140)
105003 1(192), 6(191), 1(140), 6(100)
105004 1(192), 6(191), 1(140), 6(100)
105005 1(196), 1(192), 2(176), 1(175), 1(157), 1(141), 1(107), 1(105), 1(81), 1(73), 1(54), 1(35)

SQL Server Central: Finding missing indexes in SQL Server 2005

Finding missing indexes in sql server 2005 - SQL Server Central

Select Top 5 mid.database_id, mid.object_id, mid.statement as table_name, mig.index_handle as index_handle
from 
(
select 
(user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs
) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
where
migs_adv.group_handle = mig.index_group_handle and
mig.index_handle = mid.index_handle
order by migs_adv.index_advantage DESC

Please use to find the columns that should be included in the indexes.
http://msdn2.microsoft.com/en-us/library/ms345405.aspx


we can run queries like

SELECT * FROM sys.dm_db_missing_index_details where index_hanle = 28

to find the columns on which we need to create the indexes.

SQL Server Central: SQL Server 2005: Script all Indexes

SQL Server 2005: Script all Indexes - SQL Server Central

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR 
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

SQL Server Central: usp_IndexesUnused - SQL Server 2005

usp_IndexesUnused - SQL 2k5 - SQL Server Central
checks for unused indexes (no updates or user hits). DB name is authorative.
Usage: EXEC usp_IndexesUnused

IF EXISTS (SELECT name FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[usp_Indexesunused]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_IndexesUnused]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC usp_IndexesUnused @DBName VARCHAR(255) = NULL
AS
--
--  usp_IndexesUnused.sql - Checks for unused indexes (no updates or user hits)
--  
--  2008-03-09 Pedro Lopes (NovaBase) pedro.lopes@novabase.pt
--
--  EXEC usp_IndexesUnused 
--
SET NOCOUNT ON
IF @DBName IS NULL
BEGIN
SELECT 'DB name is authorative.' AS 'WARNING - SYNTAX ERROR!'
RETURN
END
DECLARE @DBID int
SELECT @DBID = DB_ID(@DBName)
DECLARE @SQLcmd NVARCHAR(max)
SET @SQLcmd = 'USE [' + @DBName + '];
SELECT ''[' + @DBName + ']'' AS DBName,
OBJECT_NAME(a.object_id) AS ''Table'',
c.name AS ''IndexName'',
(SELECT used/128 FROM sysindexes b WHERE b.id = a.object_id AND b.name=c.name AND c.index_id = b.indid) AS ''Size_MB'',
(a.user_seeks + a.user_scans + a.user_lookups) AS ''Hits'',
RTRIM(CONVERT(NVARCHAR(10),CAST(CASE WHEN (a.user_seeks + a.user_scans + a.user_lookups) = 0 THEN 0 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups)) * 100 / 
CASE (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) WHEN 0 THEN 1 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates)) END END AS DECIMAL(18,2)))) + ''/'' +
RTRIM(CONVERT(NVARCHAR(10),CAST(CASE WHEN a.user_updates = 0 THEN 0 ELSE CONVERT(REAL, a.user_updates) * 100 / 
CASE (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) WHEN 0 THEN 1 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates)) END END AS DECIMAL(18,2)))) AS [R/W_Ratio],
a.user_updates AS ''Updates'', --indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view
a.last_user_update AS ''Update_Date''
FROM sys.dm_db_index_usage_stats a
JOIN sysobjects AS o ON (a.OBJECT_ID = o.id)
JOIN sys.indexes AS c ON (a.OBJECT_ID = c.OBJECT_ID AND a.index_id = c.index_id)
WHERE o.type = ''U''   -- exclude system tables
AND c.is_unique = 0   -- no unique indexes
AND c.type = 2    -- nonclustered indexes only
AND c.is_primary_key = 0  -- no primary keys
AND c.is_unique_constraint = 0  -- no unique constraints
AND c.is_disabled = 0   -- only active indexes
AND a.database_id = ' + CAST(@DBID AS CHAR(4)) + ' -- for current database only
AND ((a.user_seeks + a.user_scans + a.user_lookups) = 0 OR a.user_updates = 0)
ORDER BY OBJECT_NAME(a.object_id), a.user_updates'
EXEC master..sp_executesql @SQLcmd
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL Server 2005 - Encryption without the Confusion - simple-talk.com

Encryption without the Confusion - simple-talk.com
In this article, we take a look at how database encryption, a new feature in SQL Server 2005, can be used to protect database objects as well as your data. The first question is why would you bother with encrypting the database? From the perspective of Microsoft product development, providing database encryption is a checklist item for being certified by the common criteria organization. From the perspective of the end user, one can simply scan the recent news to find articles about databases that have been compromised, thru either stolen computers or hacking- database encryption is the last line of defence.
The SQL Server product team didn't re-invent the wheel to implement encryption in SQL Server; the feature is based on the Windows encryption API, which is robust and powerful.

SQL Server 2005 - Partitioned Tables - simple-talk.com

Partitioned Tables in SQL Server 2005
Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. The feature is only available for enterprise and developer edition. For other editions you can get a similar functionality with a partitioned view.
This article focuses on how to create a partitioned table and manipulate the partitions, rather than exploring the performance aspects.

SQL Server 2005: sys.dm_io_virtual_file_stats

Louis Davidson : sys.dm_io_virtual_file_stats

SQL Server 2005: sys.dm_db_file_space_usage

Louis Davidson : sys.dm_db_file_space_usage

SQL Server 2005: sys.dm_db_index_usage_stats

Louis Davidson : sys.dm_db_index_usage_stats

SQL Server 2005: sys.dm_db_partition_stats

Louis Davidson : sys.dm_db_partition_stats

SQL Server 2005: sys.dm_exec_sql_text

Louis Davidson : sys.dm_exec_sql_text

SQL Server 2005: sys.dm_db_file_space_usage

Louis Davidson : sys.dm_db_file_space_usage

SQL Server 2005: sys.dm_io_virtual_file_stats

Louis Davidson : sys.dm_io_virtual_file_stats

SQL Server 2005: sys.dm_exec_query_optimizer_info

Louis Davidson : sys.dm_exec_query_optimizer_info