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.
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
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
SELECT name
, log_reuse_wait_desc
FROM sys.databases;
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 |
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’
