Wednesday, January 27, 2016

Cost Threshold for Parallelism


Was experiencing high CPU usage (95%+) on 4 vCPU server I had "inherited".

Server has SQL Server, Reporting Services, and Integration Services running on it.

Cost Threshold for Parallelism was set to 25.  Increased it to 120 and immediately saw drop in CPU contention.




Average CPU usage dropped to %25-%30 with short lived spikes instead long running contention.
















Lowered to 60 and saw this:



















Increased in steps of 10 up to 100 until the contention was again relieved.

Monday, January 25, 2016

sp_recompile

Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.

https://msdn.microsoft.com/en-us/library/ms181647(v=sql.110).aspx

sp_refreshview

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

https://msdn.microsoft.com/en-us/library/ms187821(v=sql.110).aspx

Friday, January 15, 2016

Log Reuse Waits

   SELECT name
        , log_reuse_wait_desc
     FROM sys.databases;




  • NOTHING - vlfs available
  • CHECKPOINT - dirty pages in buffer pool, awaiting checkpoint, issue checkpoint and/or adjust RECOVERY INTERVAL setting
  • LOG_BACKUP - waiting for next transaction log backup
  • ACTIVE_BACKUP_OR_RESTORE - transaction log backup in progress or is "hung"
  • ACTIVE_TRANSACTION - open transaction, use DBCC OPENTRAN to identify
  • DATABASE_MIRRORING - secondary falls behind
  • REPLICATION - subscriber falls behind
  • DATABASE_SNAPSHOT_CREATION - transient; snapshot creation in progress
  • LOG_SCAN -  transient; fn_dblog, during a checkpoint initiates a log scan to synchronize log sequence numbers, or other process that causes a log scan
  • OTHER_TRANSIENT - currently not in use

DATABASEPROPERTYEX

DATABASEPROPERTYEX ( database, property )

database is a name of the database. It is of type nvarchar(128) 

property is an option or property setting to be returned. It is of type nvarchar(128). Below are the possible property names.

Value
Description
Returned Value
Collation
Default collation name for the database.
Collation name
IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiPaddingEnabled
Strings are padded to the same length before comparison or insert.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsArithmeticAbortEnabled
Queries are terminated when an overflow or divide-by-zero error occurs during query execution.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsMergePublished
The tables of a database can be published for replication, if replication is installed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNumericRoundAbortEnabled
Errors are generated when loss of precision occurs in expressions.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsPublished
The tables of the database can be published for snapshot or transactional replication, if replication is installed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsSubscribed
Database can be subscribed for publication.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsTornPageDetectionEnabled
Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages.
1 = TRUE
0 = FALSE
NULL = Invalid input
Recovery
Recovery model for the database.
FULL = full recovery model
BULK_LOGGED = bulk logged model
SIMPLE = simple recovery model
SQLSortOrder
SQL Server sort order ID supported in previous versions of SQL Server.
0 = Database is using Windows collation
>0 = SQL Server sort order ID
Status
Database status.
ONLINE = database is available for query
OFFLINE = database was explicitly taken offline
RESTORING = database is being restored
RECOVERING = database is recovering and not yet ready for queries
SUSPECT = database cannot be recovered
Updateability
Indicates whether data can be modified.
READ_ONLY = data can be read but not modified
READ_WRITE = data can be read and modified
UserAccess
Indicates which users can access the database.
SINGLE_USER = only onedb_owner, dbcreator, orsysadmin user at a time
RESTRICTED_USER = only members of db_owner,dbcreator, and sysadminroles
MULTI_USER = all users
Version
Internal version number of the Microsoft SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing.
Version number = Database is open
NULL = Database is closed

Return type of DATABASEPROPERTYEX function is a sql_variant.

PARTNER TIMEOUT

SQL Server uses PARTNER TIMEOUT to determine the maximum period of time an instance waits to get a "ping" message from another instance of SQL Server before determining whether a failover should occur.

To see the current setting, run this query:

   SELECT db_name(database_id) "database_name"
        , mirroring_connection_timeout
     FROM sys.database_mirroring
GO



To change the current setting, run this query:

ALTER DATABASE [databasename] SET PARTNER TIMEOUT 10
GO


lowest value: 5
default value: 10

To generate change sql:

   SELECT db_name(database_id)
        , mirroring_connection_timeout
        , 'alter database [' + db_name(database_id) 
        + '] set partner timeout 30'
     FROM sys.database_mirroring
    WHERE mirroring_role_desc = 'PRINCIPAL'