Index Information - Fragmentation level, etc...

The following queries when executed together provide the following:

tablename, objectID, indexName, rows in table, index reads, indexwrites, user seeks, user scans, user lookups, Fragmentation level and LastStatsUpdate...

create table #Frag (TableName varchar(300), IndexName varchar(700), FragPercent float)

insert into #Frag(TableName, IndexName, FragPercent)SELECT c.name, b.name,ps.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS psINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDINNER JOIN sys.objects as C on B.object_ID = C.object_IDAND ps.index_id = b.index_idWHERE ps.database_id = DB_ID()ORDER BY c.name

create table #iu(tablename varchar(200), [Object_ID] bigint, Index_name varchar(700), UserSeeks bigint, UserScans bigint, UserLookups bigint, UserUpdates bigint, RowsInTable bigint,IndexReads bigint, IndexWrites bigint)

insert into #iu(tablename, Object_ID, Index_name, UserSeeks,UserScans, UserLookups,UserUpdates, RowsInTable,IndexReads, IndexWrites)SELECT
I
USER_SEEKS
USER_SCANS
USER_LOOKUPS
USER_UPDATES OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], s.object_ID, .[NAME] AS [INDEX NAME], , , , ,
reads
writes rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id), =user_seeks + user_scans + user_lookups, = user_updates
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by OBJECT_NAME(S.[OBJECT_ID])

SELECT name AS Stats, STATS_DATE(object_id, stats_id) AS LastStatsUpdate into #a
FROM sys.stats where left(name,4)!='_WA_';

select i.tablename, i.OBJECT_ID,i.index_name, i.RowsinTable, i.indexReads, i.IndexWrites, i.UserSeeks, i.UserScans,i.UserLookups,FragPercent,LastStatsUpdate
from #iu i
inner join #frag f on i.Index_name = f.indexname and i.tablename = f.tablename
inner join #a a on a.stats = i.index_name--order by i.tablename
order by laststatsupdate

Linked Server - Distributed Transaction Issue

--Error Message

Executed as user: domainName\user. The operation could not be performed because OLE DB provider "SQLNCLI10"
for linked server "LSName" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391)
OLE DB provider "SQLNCLI10" for linked server "LSName" returned message "The partner transaction manager
has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412). The step failed.

--Fix
EXEC sp_serveroption @server = 'LSName',@optname = 'remote proc transaction promotion', @optvalue = 'False' ;

SQL 2008 - Linked Server: Msg 7391, Level 16, State 2, Line 5

Trying to leverage a linked Server and received the following error:

OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" was unable to begin a distributed transaction.

-------------------
Verify RPC and RPC out is set to True in the Linked Server properties.

Code Snippet was:

I had declared the table variable @temp2 (yada)

declare @dynamic_sql NVARCHAR(MAX)
declare @prefix NVARCHAR(MAX)
SET @prefix = N'LinkedServerName.'
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp2 EXEC ' + @prefix + 'DBName.dbo.StoredProcName '
exec (@dynamic_sql)

Still received the error message Msg 7391, Level 16, State 2, Line 5

-------------
Changed the call to use OpenQuery...
declare @dynamic_sql NVARCHAR(MAX)
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp ' + CHAR(13) +
' SELECT * FROM OPENQUERY(LinkedServerName, ' + '''' + 'DBName.dbo.StoredProcName ' + '''' + ')'

And it worked successfully...

SQL 2008 R2 Mirroring End Points - Different NICs

Leveraging dedicated NIC's for SQL Mirror NIC traffic (other than SQL Server system traffic).

Example:

SQL Services run on 10.X.X.Y on Principal and 10.X.X.Z on Mirror)
SQL Mirror traffic:

Mirror Node will run on 192.168.0.2
Principal Node will run on 192.168.000.1


Mirror Node:

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.2))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

--------------------

Principal Node

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.1))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

-----------

Restore the database on mirror node along with one transaction log...


restore database dbname
from disk = '' WITH NORECOVERY, stats = 10

Restore log dbname from disk='' with norecovery, stats=10

--On mirror node
alter database DBNAME
set partner = 'TCP://192.168.000.1:5030'

--On Principal Node
alter database entrycheck
set partner = 'TCP://192.168.000.2:5030'

By default this will create a Sync mirror.

Map SQL Logins after restore

Finally got around to sorting out orphaned users. Create two procedures... Here it is.

Put these procedures in the master database..

The second procedure loops thru each database (using the first procedure) and populates a temp table.The select statement at the end of GetOrphanedUsers_AllDatabases simply builds the string to execute.

--Here is the first proc.
CREATE Proc [dbo].[GetOrphanedUsers]
@databasename sysname
as
set nocount on

Create table #Fitz
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

declare @sql varchar(1000)
set @sql = '
use '+@databasename+'
insert into #Fitz
(DB, OrphanedUserName, UserType)
SELECT db_name() as ''DB'', a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In (''S'', ''U'', ''G'')
AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')

select * from #Fitz
'

exec (@sql)
GO

----Second Proc
CREATE PROC [dbo].[GetOrphanedUsers_AllDatabases]
AS
Create table #OUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

DECLARE @databasename sysname
DECLARE DatabaseLoop CURSOR FOR
(select name from sys.databases where state=0 and database_id >4 )
OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop into @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #OUsers
exec GetOrphanedUsers @databasename
FETCH NEXT FROM DatabaseLoop into @databasename
END
CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop

Select 'exec ' + DB + '..sp_change_users_login ' + '''' + 'auto_fix' + '''' + ',' + '''' + OrphanedUserName + ''''--*
from #OUsers
where orphanedUserName <> 'dbo'
order by db
GO

Trace Flag 834

We purchased new hardware (HP 980) with 32 cores, 50 GB RAM with multiple FusionIO cards (to house database files) to run SQL 2008 R2 Enterprise. We’re leveraging SQL mirroring. This physical server is the mirror node.

Long story short, after many rebuilds from the OS up, SQL performance remained degraded. Systematically, we began to rule out what was causing the issue.

At the end of the day, we added trace flag –T834 (causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool). Lock pages in memory had already been configured.

http://support.microsoft.com/kb/920093

Well, long story short, the performance of the SQL server immediately began to improve.

Interesting fact, we have dozens of other high performance SQL instances (clustered, stand-alone, etc.), (SAN storage, other FusionIO cards) and have NOT had to set the –T834 trace flag on any additional SQL instances.

If you’ve had to set the T834 flag, please let me know.

FusionIO SQL Server - Severe performance Degradation

Long story short - I run FusionIO cards in production (4 X 1.2TB DUO Cards for one logical drive and 2 X 640 DUO cards for 2 logical drives).

Well, come to find out the FusionIO cards need to placed specifically on the PCIE bus in a specific order. Our production servers (with the physical different drive layout) work without issues.

I builtout a new physical server (HP980) with FusionIO cards and performance was horrible.
So - after multiple server rebuilds - we mimicked the exact FusionIO physical card location from our primary principal node. Now the server behaves as expected.

Now, all the 1.2 TB Duo cards are sitting on Gen8 slots and the smaller 640 cards reside on the Gen4 slots.

So - if running FusionIO cards, make sure the cards in the correct Gen slot.