One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes
  • Non clustered indexes and include column effect on index structure
  • Heap
  • Heap vs Clustered
  • Various DMV utilization to understand index internals
  • How to detect fragmentation
  • Logical fragmentation and effects on query perfromance
  • Page splits , Page density, page fullness

Statistics

  • How to view statistics
  • Effect of statistics on query performance
  • effect of stats from index rebuild and reorg

Log usage

  • Index rebuild vs reorg
  • Log usage for Index rebuilds and reorganize

Over all must watch

 

How to resolve suspect Database issue (Emergency Repair) SQL Server

There are several reasons why database goes into suspect mode, but best way to resond that I found very helpfull

1) Set the database to emergencey mode

ALTER DATABASE ‘db name’ SET EMERGENCY

2) Set the database to Single user mode so to do dbcc repair

ALTER DATABASE DAXBI SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

3) Run dbcc repair command

DBCC CHECKDB (N’db name’, REPAIR_REBUILD ) WITH ALL_ERROMSGS, NO_INFOMSGS;

that will take care of any consistency errors with indexes and partitions

if you want the database to be back online not worried about the data loss

DBCC CHECKDB (‘db name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERROMSGS, NO_INFOMSGS;

4) Set databsae to multi user mode

ALTER DATABASE ‘db name’ SET MULTI_USER

Unspecified Error SSIS Excel connection manager

When you create a data flow with an Excel destination and click ‘Name of Excel Sheet’ I get an “Unspecified Error”.  When you look under ‘Show Advanced Editor’ of the destination you will see following erros

Error at blah [Connection manager ""]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Unspecified error". Error at Insert blah [Destination - blah [199]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

To Fix this three easy steps

First step:

please make sure you have this files

C:\windows\system32\odbcjt32.dll

C:\windows\system32\msjet40.dll

C:\windows\system32\msexcl40.dll

C:\Program Files\Common Files\System\Ole DB\oledb32.dll

C:\Program Files\Common Files\System\ado\msado15.dll

 

Second Step

If they are, please manually register them.  Run each of the following commands from command prompt:

Regsvr32 "C:\Program Files\Common Files\system\Ole DB\oledb32.dll"

Regsvr32 "C:\Program Files\Common Files\system\ado\msado15.dll"

Regsvr32 "C:\windows\system32\msjet40.dll"

Regsvr32 "C:\windows\system32\msexcl40.dll"

 

Out put should look like below do this for all the commands above

Unspecified error ssis excel

 

Step three : Restart Visual studio developer studio

 

 

 

DMV : sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name Data type Description
session_id int Identifies the session associated with this connection. Is nullable.
connect_time datetime Timestamp when connection was established. Is not nullable.
num_reads int Number of packet reads that have occurred over this connection. Is nullable.
num_writes int Number of data packet writes that have occurred over this connection. Is nullable.
last_read datetime Timestamp when last read occurred over this connection. Is nullable.
last_write datetime Timestamp when last write occurred over this connection. Not Is nullable.
client_net_address varchar(48) Host address of the client connecting to this server. Is nullable.

sys.dm_exec_connections

–Client wich has more than one connection

SELECT  client_net_address ,COUNT(*) number_of_connections

FROMsys.dm_exec_connections

GROUP BY client_net_address

HAVING  COUNT(*)> 1

ORDER  BY number_of_connections desc

–Oldest connection to server

SELECT  session_id,connection_id,connect_time FROMsys.dm_exec_connections

ORDER  BY connect_time asc

–Connection with highest network utilization

SELECT  num_reads,num_writes FROMsys.dm_exec_connections

ORDER  BY num_reads DESC,num_writes DESC

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

agent error collector upload error

Executing the query “{call [core].[sp_create_snapshot](?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

if we look sp_create_snapshot procedure in Management_dataware_house  it accepts six parameters  @collection_set_uid   ,@collector_type_uid   ,@machine_name  , @named_instance  ,  @log_id   ,  @snapshot_id

this procedure creates a snapshot in the management datawarehouse for a collection instance one parameter is Collecter_type_id

Note: Sql Server Data collector can collect data from Dynamic management views , SQL trace and Windows performance counters using TSQL and WMI query’s

We can get list of availanle data collector types by querying

SELECT

[collector_type_uid],[name]

,[parameter_schema]

,[parameter_formatter]

,[schema_collection]

,[collection_package_name]

,[collection_package_folderid]

,[upload_package_name]

,[upload_package_folderid]

,[is_system]

FROM [msdb].[dbo].[syscollector_collector_types_internal]

Collector types

insertinto [core].[supported_collector_types_internal](collector_type_uid)

values (’14AF3C12-38E6-4155-BD29-F33E7966BA23′)

 

collector suscces full