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’
