Baseline Collector Solution V2 available for download

Finally I have finished Baseline Collector Solution V2.

Article for introducing some of its new features, improvements is coming soon on SQLServerCentral.com.

You can read about Baseline Collector Solution V1 here.

Download Baseline Collector Solution V2

The Solution is FREE!

Written by Robert Virag

Comprehensive Baseline Collecting Solution

Change History

Changes in V2 – 2015.08.14

  • Features
    • Central Configuration
  • Improvements
    • Version compatibility: 2008+
    • [dbo].[sp_CollectFileInfo]: Redesigned – Semi-Dynamic Collector
    • [dbo].[sp_CollectInstanceInfo]: collects trace flag status info
    • [dbo].[sp_CollectPerfmonData]: skip ‘WAITFOR DELAY @sample_interval’ for the last loop
    • [dbo].[sp_CollectTempDBUsage]: skip ‘WAITFOR DELAY @sample_interval’ for the last loop
    • Help info: Dynamic type and default values (Default CustomConfig)!
    • New Jobs
      • Baseline – Collect InstanceInfo/ConfigData/DatabaseInfo/FileInfo/WaitStats
      • Baseline – Output File Cleanup
      • with recommended schedules
    • Setup logging
  • New Schedules added
    • () baseline – weekend – every 30 mins
    • () baseline – weekend – every 30 mins – BT/6AM-7PM/
    • () baseline – weekend – every 1 hour – BT/6AM-7PM/
    • () baseline – every 4 weeks – 1week – every 30 mins
    • () baseline – every 4 weeks – 1week – every 30 mins – BT/6AM-7PM/
    • () baseline – every 4 weeks – 1week – every 1 hour
    • () baseline – every 4 weeks – 1week – every 1 hour – BT/6AM-7PM/
    • () baseline – every 4 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 4 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 4 weeks – 1week – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 4 weeks – 1week – evey 12 hours /6AM/6PM/
    • () baseline – every 4 weeks – weekdays – every 30 mins
    • () baseline – every 4 weeks – weekdays – every 30 mins – BT/6AM-7PM/
    • () baseline – every 4 weeks – weekdays – every 1 hour
    • () baseline – every 4 weeks – weekdays – every 1 hour – BT/6AM-7PM/
    • () baseline – every 4 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 4 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 4 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 4 weeks – weekdays – evey 12 hours /6AM/6PM/
    • () baseline – every 4 weeks – weekend – every 30 mins
    • () baseline – every 4 weeks – weekend – every 30 mins – BT/6AM-7PM/
    • () baseline – every 4 weeks – weekend – every 1 hour
    • () baseline – every 4 weeks – weekend – every 1 hour – BT/6AM-7PM/
    • () baseline – every 4 weeks – weekend – every 3 hours  12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 4 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 4 weeks – weekend – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 4 weeks – weekend – evey 12 hours /6AM/6PM/
    • () baseline – every 12 weeks – 1week – every 30 mins
    • () baseline – every 12 weeks – 1week – every 30 mins – BT/6AM-7PM/
    • () baseline – every 12 weeks – 1week – every 1 hour
    • () baseline – every 12 weeks – 1week – every 1 hour – BT/6AM-7PM/
    • () baseline – every 12 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 12 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 12 weeks – 1week – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 12 weeks – 1week – evey 12 hours /6AM/6PM/
    • () baseline – every 12 weeks – weekdays – every 30 mins
    • () baseline – every 12 weeks – weekdays – every 30 mins – BT/6AM-7PM/
    • () baseline – every 12 weeks – weekdays – every 1 hour
    • () baseline – every 12 weeks – weekdays – every 1 hour – BT/6AM-7PM/
    • () baseline – every 12 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 12 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 12 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 12 weeks – weekdays – evey 12 hours /6AM/6PM/
    • () baseline – every 12 weeks – weekend – every 30 mins
    • () baseline – every 12 weeks – weekend – every 30 mins – BT/6AM-7PM/
    • () baseline – every 12 weeks – weekend – every 1 hour
    • () baseline – every 12 weeks – weekend – every 1 hour – BT/6AM-7PM/
    • () baseline – every 12 weeks – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 12 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 12 weeks – weekend – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 12 weeks – weekend – evey 12 hours /6AM/6PM/
    • () baseline – every 24 weeks – 1week – every 30 mins
    • () baseline – every 24 weeks – 1week – every 30 mins – BT/6AM-7PM/
    • () baseline – every 24 weeks – 1week – every 1 hour
    • () baseline – every 24 weeks – 1week – every 1 hour – BT/6AM-7PM/
    • () baseline – every 24 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 24 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 24 weeks – 1week – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 24 weeks – 1week – evey 12 hours /6AM/6PM/
    • () baseline – every 24 weeks – weekdays – every 30 mins
    • () baseline – every 24 weeks – weekdays – every 30 mins – BT/6AM-7PM/
    • () baseline – every 24 weeks – weekdays – every 1 hour
    • () baseline – every 24 weeks – weekdays – every 1 hour – BT/6AM-7PM/
    • () baseline – every 24 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 24 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 24 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 24 weeks – weekdays – evey 12 hours /6AM/6PM/
    • () baseline – every 24 weeks – weekend – every 30 mins
    • () baseline – every 24 weeks – weekend – every 30 mins – BT/6AM-7PM/
    • () baseline – every 24 weeks – weekend – every 1 hour
    • () baseline – every 24 weeks – weekend – every 1 hour – BT/6AM-7PM/
    • () baseline – every 24 weeks – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
    • () baseline – every 24 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
    • () baseline – every 24 weeks – weekend – every 8 hours /6AM/2PM/10PM/
    • () baseline – every 24 weeks – weekend – evey 12 hours /6AM/6PM/

V1.2 – 2015.05

  • Bug Fixes

V1.1 – 2015.04

  • Bug Fixes
  • New Collector: sp_CollectInstanceInfo
  • Additional Schedules*

V1 Original Release – 2014.09.13

* – Added in V1.1
** – Added in V2

Tables:

  • * [dbo].[instance_info]
  • [dbo].[configuration_data]
  • [dbo].[database_info]
  • [dbo].[file_info]
  • [dbo].[iovf_stats]
  • [dbo].[perfmon_data]
  • [dbo].[tempdb_usage]
  • [dbo].[wait_stats]
  • [dbo].[ts_file_info]
  • [dbo].[ts_iovf_stats]
  • [dbo].[ts_perfmon_data]
  • [dbo].[ts_tempdb_usage]
  • [dbo].[ts_wait_stats]
  • [dbo].[filter_database_file]
  • [dbo].[filter_performance_counters]
  • [dbo].[filter_wait_types]
  • ** [dbo].[bcs_config]

Collectors:

  • * sp_CollectInstanceInfo
  • sp_CollectConfigData
  • sp_CollectDatabaseInfo
  • sp_CollectFileInfo
  • sp_CollectIOVFStats
  • sp_CollectPerfmonData
  • sp_CollectTempDBUsage
  • sp_CollectWaitStats

Functions/Procedures:

  • ** sp_ConfigureBCS

Jobs:

  • ** Baseline – Collect InstanceInfo/ConfigData/DatabaseInfo/FileInfo/WaitStats
    • SCHEDULE[baseline – daily – every 6 hours /6AM/12PM/6PM/12AM/]
    • STEPS:
      • Collect InstanceInfo
      • Collect ConfigData
      • Collect DatabaseInfo
      • Collect FileInfo
      • Collect WaitStats with @ResetWaitStats=1
      • JobCheck
  • Baseline – CollectIOVFStats
    • SCHEDULE[baseline – every 4 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/]
  • Baseline – CollectPerfmonData
    • SCHEDULE[baseline – every 4 weeks – 1week – every 1 hour – BT/6AM-7PM/]
  • Baseline – CollectTempDBUsage
    • SCHEDULE[baseline – every 4 weeks – 1week – every 1 hour – BT/6AM-7PM/]
  • ** Baseline – Output File Cleanup
    • SCHEDULE[baseline – daily – at 2351PM]

Schedules:

  • baseline – daily – at 2359PM
  • baseline – daily – at 2355PM
  • * baseline – daily – at 2351PM
  • * baseline – daily – every 30 mins
  • * baseline – daily – every 30 mins – BT/6AM-7PM/
  • baseline – daily – every 1 hour
  • baseline – daily – every 1 hour – BT/6AM-7PM/
  • * baseline – daily – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • baseline – daily – every 6 hours /6AM/12PM/6PM/12AM/
  • baseline – daily – every 8 hours /6AM/2PM/10PM/
  • baseline – daily – evey 12 hours /6AM/6PM/
  • * baseline – weekday – every 30 mins
  • * baseline – weekday – every 30 mins – BT/6AM-7PM/
  • baseline – weekday – every 1 hour
  • baseline – weekday – every 1 hour – BT/6AM-7PM/
  • * baseline – weekday – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • baseline – weekday – every 6 hours /6AM/12PM/6PM/12AM/
  • baseline – weekday – every 8 hours /6AM/2PM/10PM/
  • baseline – weekday – evey 12 hours /6AM/6PM/
  • ** baseline – weekend – every 30 mins
  • ** baseline – weekend – every 30 mins – BT/6AM-7PM/
  • baseline – weekend – every 1 hour
  • ** baseline – weekend – every 1 hour – BT/6AM-7PM/
  • baseline – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • baseline – weekend – every 6 hours /6AM/12PM/6PM/12AM/
  • baseline – weekend – every 8 hours /6AM/2PM/10PM/
  • baseline – weekend – evey 12 hours /6AM/6PM/
  • ** baseline – every 4 weeks – 1week – every 30 mins
  • ** baseline – every 4 weeks – 1week – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 4 weeks – 1week – every 1 hour
  • ** baseline – every 4 weeks – 1week – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 4 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 4 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 4 weeks – 1week – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 4 weeks – 1week – evey 12 hours /6AM/6PM/
  • ** baseline – every 4 weeks – weekdays – every 30 mins
  • ** baseline – every 4 weeks – weekdays – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 4 weeks – weekdays – every 1 hour
  • ** baseline – every 4 weeks – weekdays – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 4 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 4 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 4 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 4 weeks – weekdays – evey 12 hours /6AM/6PM/
  • ** baseline – every 4 weeks – weekend – every 30 mins
  • ** baseline – every 4 weeks – weekend – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 4 weeks – weekend – every 1 hour
  • ** baseline – every 4 weeks – weekend – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 4 weeks – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 4 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 4 weeks – weekend – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 4 weeks – weekend – evey 12 hours /6AM/6PM/
  • ** baseline – every 12 weeks – 1week – every 30 mins
  • ** baseline – every 12 weeks – 1week – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 12 weeks – 1week – every 1 hour
  • ** baseline – every 12 weeks – 1week – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 12 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 12 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 12 weeks – 1week – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 12 weeks – 1week – evey 12 hours /6AM/6PM/
  • ** baseline – every 12 weeks – weekdays – every 30 mins
  • ** baseline – every 12 weeks – weekdays – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 12 weeks – weekdays – every 1 hour
  • ** baseline – every 12 weeks – weekdays – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 12 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 12 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 12 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 12 weeks – weekdays – evey 12 hours /6AM/6PM/
  • ** baseline – every 12 weeks – weekend – every 30 mins
  • ** baseline – every 12 weeks – weekend – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 12 weeks – weekend – every 1 hour
  • ** baseline – every 12 weeks – weekend – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 12 weeks – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 12 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 12 weeks – weekend – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 12 weeks – weekend – evey 12 hours /6AM/6PM/
  • ** baseline – every 24 weeks – 1week – every 30 mins
  • ** baseline – every 24 weeks – 1week – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 24 weeks – 1week – every 1 hour
  • ** baseline – every 24 weeks – 1week – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 24 weeks – 1week – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 24 weeks – 1week – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 24 weeks – 1week – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 24 weeks – 1week – evey 12 hours /6AM/6PM/
  • ** baseline – every 24 weeks – weekdays – every 30 mins
  • ** baseline – every 24 weeks – weekdays – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 24 weeks – weekdays – every 1 hour
  • ** baseline – every 24 weeks – weekdays – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 24 weeks – weekdays – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 24 weeks – weekdays – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 24 weeks – weekdays – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 24 weeks – weekdays – evey 12 hours /6AM/6PM/
  • ** baseline – every 24 weeks – weekend – every 30 mins
  • ** baseline – every 24 weeks – weekend – every 30 mins – BT/6AM-7PM/
  • ** baseline – every 24 weeks – weekend – every 1 hour
  • ** baseline – every 24 weeks – weekend – every 1 hour – BT/6AM-7PM/
  • ** baseline – every 24 weeks – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
  • ** baseline – every 24 weeks – weekend – every 6 hours /6AM/12PM/6PM/12AM/
  • ** baseline – every 24 weeks – weekend – every 8 hours /6AM/2PM/10PM/
  • ** baseline – every 24 weeks – weekend – evey 12 hours /6AM/6PM/

Tested: 2008 / 2008 R2 / 2012 / 2014

Summary:
Collect SQL Server Baseline (Raw) Data

—-

For scheduling the collectors with agent jobs, Ola Hallengren’s code is used
with his permission.

http://ola.hallengren.com/license.html

License:

Copyright (c) 2014 Robert Virag | SQLApprentice.net

Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the “Software”), to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Posted in baseline, BaselineCollectorSolution | Leave a comment

Update for Baseline Collector Solution (V1.2)

Still “just” bugfixes, but V2 is just around the corner! So Stay-tuned! Bigger update is coming very soon… 😉

Thank all of you for the feedbacks!

New features coming in  Baseline Collector Solution V2

  • Easier collector management with Central Configuration
  • More effective, re-worked sp_CollectFileInfo 😉

Download Comprehensive Baseline Collecting Solution V1.2

Change History

Changes in V1.2 – 2015.05

  • Support
    • Please notice: For BCS V1.* no new features are coming, only bugfixes!!!
  • BugFix
    • sp_CollectDatabaseInfo – table [database_info] collation_name data type is changed to VARCHAR(128) to allow NULL values in case of the database is not in ONLINE state or AUTO CLOSE option is ON.
    • sp_CollectTempDBUsage – no comment… just: “shame on me” :(

Changes in V1.1 – 2015.04

  • Collectors
    • New collector: sp_CollectInstanceInfo
  • New Schedules added
    • marked with ‘*’ below
  • BugFix
    • sp_CollectDatabaseInfo – SQL2014
    • @Retention = NULL
    • CollectingInterval/SampleInterval/MeasuringInterval type, possible value range
    • AI_SQLTRACE_INCREMENTAL_FLUSH_SLEEP
    • Job output file name (@OutputFile0X)
    • sp_CollectWaitStats – @ResetWaitStats = 0 If @MeasuringInterval is specified

V1 Original Release – 2014.09

—//—

Collectors:

sp_CollectConfigData
sp_CollectDatabaseInfo
sp_CollectFileInfo
sp_CollectIOVFStats
sp_CollectPerfmonData
sp_CollectTempDBUsage
sp_CollectWaitStats
*sp_CollectInstanceInfo

Schedules:

baseline – daily – at 2359PM
baseline – daily – at 2357PM
baseline – daily – at 2355PM
*baseline – daily – at 2353PM
*baseline – daily – at 2351PM

*baseline – daily – every 5 mins
*baseline – daily – every 5 mins – BT/6AM-7PM/
baseline – daily – every 10 mins
baseline – daily – every 10 mins – BT/6AM-7PM/
baseline – daily – every 15 mins
baseline – daily – every 15 mins – BT/6AM-7PM/
*baseline – daily – every 30 mins
*baseline – daily – every 30 mins – BT/6AM-7PM/
baseline – daily – every 1 hour
baseline – daily – every 1 hour – BT/6AM-7PM/
*baseline – daily – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – daily – every 6 hours /6AM/12PM/6PM/12AM/
baseline – daily – every 8 hours /6AM/2PM/10PM/
baseline – daily – evey 12 hours /6AM/6PM/

*baseline – weekday – every 5 mins
*baseline – weekday – every 5 mins – BT/6AM-7PM/
baseline – weekday – every 10 mins
baseline – weekday – every 10 mins – BT/6AM-7PM/
baseline – weekday – every 15 mins
baseline – weekday – every 15 mins – BT/6AM-7PM/
*baseline – weekday – every 30 mins
*baseline – weekday – every 30 mins – BT/6AM-7PM/
baseline – weekday – every 1 hour
baseline – weekday – every 1 hour – BT/6AM-7PM/
*baseline – weekday – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – weekday – every 6 hours /6AM/12PM/6PM/12AM/
baseline – weekday – every 8 hours /6AM/2PM/10PM/
baseline – weekday – evey 12 hours /6AM/6PM/

*baseline – weekend – every 5 mins
baseline – weekend – every 10 mins
baseline – weekend – every 15 mins
baseline – weekend – every 30 mins
baseline – weekend – every 1 hour
baseline – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – weekend – every 6 hours /6AM/12PM/6PM/12AM/
baseline – weekend – every 8 hours /6AM/2PM/10PM/
baseline – weekend – evey 12 hours /6AM/6PM/

 

Posted in Uncategorized | Leave a comment

Update for Baseline Collector Solution (V1.1)

This update “is just” a bunch of bugfixes plus a new collector: sp_CollectInstanceInfo

Stay-tuned! Bigger update is coming very soon… 😉

Download Comprehensive Baseline Collecting Solution V1.1

Change History

Changes in V1.1 – 2015.04

  • Collectors
    • New collector: sp_CollectInstanceInfo
  • New Schedules added
    • marked with ‘*’ below
  • BugFix
    • sp_CollectDatabaseInfo – SQL2014
    • @Retention = NULL
    • CollectingInterval/SampleInterval/MeasuringInterval type, possible value range
    • AI_SQLTRACE_INCREMENTAL_FLUSH_SLEEP
    • Job output file name (@OutputFile0X)
    • sp_CollectWaitStats – @ResetWaitStats = 0 If @MeasuringInterval is specified

V1 Original Release – 2014.09

—//—

Collectors:

sp_CollectConfigData
sp_CollectDatabaseInfo
sp_CollectFileInfo
sp_CollectIOVFStats
sp_CollectPerfmonData
sp_CollectTempDBUsage
sp_CollectWaitStats
*sp_CollectInstanceInfo

Schedules:

baseline – daily – at 2359PM
baseline – daily – at 2357PM
baseline – daily – at 2355PM
*baseline – daily – at 2353PM
*baseline – daily – at 2351PM

*baseline – daily – every 5 mins
*baseline – daily – every 5 mins – BT/6AM-7PM/
baseline – daily – every 10 mins
baseline – daily – every 10 mins – BT/6AM-7PM/
baseline – daily – every 15 mins
baseline – daily – every 15 mins – BT/6AM-7PM/
*baseline – daily – every 30 mins
*baseline – daily – every 30 mins – BT/6AM-7PM/
baseline – daily – every 1 hour
baseline – daily – every 1 hour – BT/6AM-7PM/
*baseline – daily – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – daily – every 6 hours /6AM/12PM/6PM/12AM/
baseline – daily – every 8 hours /6AM/2PM/10PM/
baseline – daily – evey 12 hours /6AM/6PM/

*baseline – weekday – every 5 mins
*baseline – weekday – every 5 mins – BT/6AM-7PM/
baseline – weekday – every 10 mins
baseline – weekday – every 10 mins – BT/6AM-7PM/
baseline – weekday – every 15 mins
baseline – weekday – every 15 mins – BT/6AM-7PM/
*baseline – weekday – every 30 mins
*baseline – weekday – every 30 mins – BT/6AM-7PM/
baseline – weekday – every 1 hour
baseline – weekday – every 1 hour – BT/6AM-7PM/
*baseline – weekday – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – weekday – every 6 hours /6AM/12PM/6PM/12AM/
baseline – weekday – every 8 hours /6AM/2PM/10PM/
baseline – weekday – evey 12 hours /6AM/6PM/

*baseline – weekend – every 5 mins
baseline – weekend – every 10 mins
baseline – weekend – every 15 mins
baseline – weekend – every 30 mins
baseline – weekend – every 1 hour
baseline – weekend – every 3 hours /12AM/3AM/6AM/9AM/12PM/3PM/6PM/9PM/
baseline – weekend – every 6 hours /6AM/12PM/6PM/12AM/
baseline – weekend – every 8 hours /6AM/2PM/10PM/
baseline – weekend – evey 12 hours /6AM/6PM/

 

Posted in baseline, Script | 2 Comments

Comprehensive Baseline Collector Solution get published on SQLServerCentral

My free Comprehensive Baseline Collector Solution has been published on SQLServerCentral.

Posted in baseline, maintenance, Project, Script, SQL | Leave a comment

Project No. 2. – Restore Script Generator

I think it is not the first time you hear that how important is to have a restore script which helps you in DR (Disaster Recovery) scenarios. In a DR scenario one of your main enemy is the time.

You have to figure out what to be done. When you decide that restore is the way to go you have to make the appropriate backup files ready (paying attention to the restore sequence) to restore then start the restore process.

Starting this process in the GUI (Management Studio) is time consuming even if you need to restore only one database. Using a restore script could be faster but only if you don’t need to write the whole script from scratch. In my opinion the best way is to have a well working restore script generator.

I tried to find a good one in the past more times. Since I did not find a script which meets fully my requirements I decided to write my own script, but not from scratch. I used the good parts, ideas from other scripts I found. My Restore Script Generator (sp_RestoreScriptGen) is inspired by Tibor Karaszi (sqlblog | blog)

sp_RestoreScriptGen generates the restore script from the backup files. It supports databases which have more data files. You can choose different destination folder for data files and log file, you can also change the database name.

Supported WITH options: REPLACE, STOPAT, RECOVERY (1), NORECOVERY (0), CHECKSUM (if available).

Only backup files with ONE backup set is supported at the moment!

Further features: Instant File Initialization check, Orphan user check, Restore Time Measure option

Syntax:

Here is the stored procedure: sp_SQLApprenticeDOTnet_SQL2008R2_RestoreScriptGen

I really hope that you can find this stored procedure useful. Don’t hesitate to contact me (robertATsqlapprenticeDOTnet) in case of having any questions or remarks.

Posted in Disaster Recovery, restore, Script | Leave a comment

Real life scenario for collecting data using linked servers get published on SQLServerCentral

My real life example how you can collect data from SQL Server instances using linked servers has been published on SQLServerCentral (When the reporting team needs an assist…). It is also a good example for practical usage of sp_uforeach (additional link) script.

Posted in Publication, Script, sp_uforeach, SQL | Leave a comment

sp_uforeach get published on SQLServerCentral

You can find my sp_uforeach script on SQLServerCentral as well: Universal foreach aka sp_uforeach

Posted in Project, Script, sp_uforeach, SQL | Leave a comment

Bug: DBCC CHECKDB data purity checks are skipped for master and model

It has been a long time since I wrote a post. I had so so much work to do, so that I had no time for posting :(

Anyway I want to change that in the future. I don’t promise that I will write every single week but I will do my best.

So about the bug. Last week, my colleague reported me that our consistency checking job was failed. More interestingly it failed on the master. Even more interestingly he said, when he tried to run the check manually with

the check run without any error. The error come up only when he used DATA_PURITY option:

This is what attracted my attention. I checked BOL which sait: “For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.”

Our affected instance is a ‘native’ SQL Server 2008 and I double-checked that so as the result of the DBCC CHECKDB command. Ok, from that time it was obvious to me that something is wrong. Maybe the way DBCC CHECKDB is working or the description of DATA_PURITY option in BOL or something else. Since we were dealing with system table inconsistency in the master database I contacted Paul Randal who is the expert of this field (too). Paul confirmed that there is bug here. See more about this bug on his blog…

Posted in bug, DBCC, SQL | Leave a comment

Deadlock scenario: ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS index options

Last month I helped one of my customer investigate a deadlock situation.

After we got the deadlock report it turned out that we were dealing with a ‘conversion deadlock’ caused by inappropriate index options.

Let’s reproduce and check the situation. I create a database ‘deadlock’, a table ‘t1’ and put some data into it. After that I create a user stored procedure which is used for updating the content of this table.

Notice that ALLOW_ROW_LOCKS=OFF and ALLOW_PAGE_LOCKS=OFF options have been used! Configuring both options to OFF means that SQL Server put every lock on the table immediately. This cause serious problems in case of high concurrency, especially for writers like in our case. Let’s go on.

Ok, we have everything to reproduce the issue. Only one thing left. Configure the profiler to catch every deadlock and set the T1222 trace flag on to get the deadlock information in the error log.

Done. Open two query window and run the following queries in it. It is important that the queries run totally the same time!

If everything go well (hm… I mean wrong in our case) you can see the following results:

Deadlock graph:

 

Deadlock report from error log:

So what happened here? Both transaction acquire an intent exclusive lock on the table (IX) because of the index options. Two IX is compatible each other. The real problem happen when both transaction want to convert its IX lock to X. Both transaction try to update totally different rows which rows could be on totally different pages, it doesn’t matter, because locks go to the top of table.

If  ALLOW_PAGE_LOCK was configured to ON the situation would be better a bit. In this case deadlock would occur if two concurrent transaction tried to update rows on the same page at totally the same time.

Conclusion: In case of high concurrency (especially writers) set ALLOW_PAGE_LOCK and ALLOW_ROW_LOCK to ON!

If you have any remark or question feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Posted in Deadlock, Index, SQL | 3 Comments

Security vs. Monitoring – Solution by using module signing

In this post, I provide my solution for the Security vs. Monitoring puzzle. I will use module signing.

First, let’s think a little bit proactive and I assume that there will be more special request in the future when we need to create some further code for monitoring, so I create a separate database called ‘Monitoring’. The ‘monitoring’ user will have rights only in this database, and just as much as it really needed.

After that I create a stored procedure called ‘usp_monitor’ with EXECUTE AS clause and in this user defined stored procedure the sp_monitor is called. I grant execute permission to the ‘monitoring’ user.

Nothing special so far. As I mentioned before, I use module signing to provide all the necessary permissions. For this I create a certificate in the ‘monitoring’ database then I sign the usp_monitoring with this certificate. After that I backup this certificate and then delete the primary key part so no-one can sign again the stored procedure in case of any modification without the primary key.

In the end, I restore the public part of the certificate to the master database, and I create a login from it, and grant authenticate server privilege to this login.

We are done. Only one thing left. Move the certificate (especially the private key part) to secure place. If you have any question feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Further on I will provide some very useful links/articles about this topic.

Posted in Security | Leave a comment