At PartitionDB we always try to improve and simplify processes.
We built a useful analysis tool that allows tracing and monitoring data without any unnecessary installations. No log file is used, meaning that the analysis span starts with the most recent time that service was uploaded. This post goes over the different parameters that are generated in the system analysis. This tool is available by downloading PdbLogic database.

In order to get all this valuable information about your system, run the following command:

exec PdbLogic..PdbanalyzeDatabase @TargetDatabaseName = ‘PdbLogic’

Remember to set TargetDatabaseName parameter to your database name.

This command will output the system analysis as shown below. The returned data is divided to seventeen categories. Please continue reading to learn more about the returned analysis.

The next paragraph explains in details each and every parameters that is returned by the function. As said before, parameters are divided to seventeen categories. If you are interested to view some of the categories only, you can pass to the PdbanalyzeDatabase function the numbers of categories that you wish to see (@IncludeGrids = ‘4’ will display category 4 only), or wish to exclude from your view (using @ExcludeGrids):

exec PdbLogic..PdbanalyzeDatabase @TargetDatabaseName = ‘PdbLogic’, @includeGrids = 4

Passing 4 to @includeGrids will display only information related to server metrics. Please refer to the next paragraph to learn more about the different categories of the returned information.

What kind of information do we get?

The analysis returns information in seventeen categories. Categories vary from machine and server information to database, tables information and more.

Below is the full list of parameters that is returned by the function. Having this information can help you find problems in your database, and gives you guidance how to improve your database performance.

Note that many of the parameters are in terms of percents. All ratios are against the server. We believe a ratio gives a much better sense to the data as oppose to just numbers.

  1. MachineName – Name of the machine
  2. StartTime – Time the machine started
  3. CPUCount – Total machine CPU count
    Recommendation is 4 CPUs for small-medium systems, 8 for medium and 16-24 for large
  4. CPUUsage – The average CPU usage for all machine’s CPUs
    Ideally should be in the range of 15-90 %
  5. CPUAlert – Cpu usage notification, ranges from ‘Very low’ to ‘Critical’
    A warning will be shown if usage is above 90 %. If usage is above 95 %, the situation is critical
  6. MemorySize Total machine memory size
    Recommendation is 2-4 GB for small-medium systems, 8-16 GB for medium, 24-32 GB for large
  7. MemoryUsage – Machine’s memory usage
    Memory usage should be in the range of 15-90 %
  8. MemoryAlert – Memory usage notification
    A warning wil be shown if useage is above 90 %. If useae is above 95 %, the situation is critical
  9. StorageSize – Total machine storage size.
    The storage size depends on the growing rate of the database. Typically, storage size is at least 50 % of the total size.
  10. StorageFreeUnallocated – Total free machine storage size
  11. StorageUsage – Machine storage usage
    Storage usage is Ideally below 90 %
  12. StorageAlert  – Storage usage notification
    Warning will be shown if storage usage is above 90 %. If usage is above 95 % the situation is critical

  1. ServerName – Name of servers
  2. SQL Server Version – Version and Service Pack of SQL server
  3. Edition – Type of edition. Can be either Enterprise, Standard or Express
  4. StartTime – The time the service has started, in other words, when the system was recently booted
  5. AgentStartTime – If agent is on, this field shows the time the agent service has started. If agent is not running, this field is empty.
    Note that if running jobs and not scheduled tasks, the agent should be turned on
  6. CPUUsed  – Number of CPU used by the SQL Server
    Ideally, CPU count equals to CPUCount. Note that if using Express edition, CPUUsed is limited to one.
  7. MemoryUsed – Amount of memory used by the server. This includes all memory types that are used such as buffer pool, procedure cache, memory manager etc.
  8. MemoryGrowth – Precentage of memory useage. How much is consumed out of the target memory
  9. MemoryTarget – Amount of maximum server memory that can be consumed by SQL server
  10. MemoryRecommended – Amount of recommended memory
    This minimum value for this parameter is usually 60% of the total machine memory size. When working in a single instance dedicated on-premise machine, the recommended memory is 60-85% of the total memory

  1. ServerName – Name of servers
  2. BufferSize – Size of buffer pool.
    Buffer pool is the main part in the memeory. Memory is used for logical reads/writes of table
  3. ResourceBufferSize – Size of resource buffer
    This buffer stores metadata
  4. TempBufferSize – Total size of temp buffer
    The amount of memory consumed by temp buffer from BufferSize. If the value is high it means you have running temporary objects
  5. MaxDataSize – Maximum database size
  6. DataSize – Total size of all databases resides on the server
  7. DataStallTime – Total IO stall time. How much time was spent on I/O waits
    If DataStallTime is high you might have IO/network storage or latch issues
  8. MaxLogSize – Size of the biggest log file on the server
    If the log file is too big, a backup or shrink should be considered
  9. LogSize – Total size of all databases log files reside on the server
  10. LogStallTime – Total IO stall time. How much time the server experienced stalling.
    If LogStallTime is high, there may be a IO or network storage or latch issues
  11. TempDataSize – Size of tempdb.
    tempdb is used by temp tables and procedures or execution plans. If tempdb is too big, it means tempdb is used a lot, on the account of the database
  12. TempDataStallTime – Stall time of tempdb.
    IO operation to and from tempdb are usually faster than than a normal database.
  13. TempLogSize – Size of log file of tempdb
    Size of up to 4 GB is reasonable
  14. TempLogStallTime – Stall time of tempdb log file
    If tempdb has IO issues, a SSD may be consdiered. Usually writes to tempdb are faster than normal database
  15. TempDataFiles – Number of tempdb files
    The number of tempdb files is important for the concurrent processes to run temporary operations.
  16. TempDataFilesRecommended – The number of recommended tempdb files based on CPU
    It is recommended to have up to eight data files per CPU. The function provides a recommended number of files for best performance

  1. ServerName – Name of server
  2. BufferCacheHitRatio – Ratio between amount of data that is retrieved from buffer pool to the total amount of data retrieved (from buffer pool and from IO requests).
    The best case would be to have 90% of the data in buffer pool. For OLTP the percentage should not be lower than 90%. If it is, it means that the database is busy with IO operations.
  3. PageExpectancyTime – The average time a page resides in the buffer pool.
    In general, the longer the page is in the buffer pool, the better the performance. If this parameter is less than five minutes it means there is too much batch processing and/or adhoc queries
  4. AverageWaitTime – The average wait time a lock request that resulted in a wait
    Generally, waits are caused by concurrent object locks (logical and physical). However, they can also be caused due to hardware-network issues
  5. ActualTime – Total live time on the server (since it is up)
  6. ElapsedTime – Total operation time since the server start
  7. WaitTime – Total wait time since the server started
  8. WorkerTime – The total time of running CPU operations since the system started
  9. WorkerUsage – Percentage of usage that is calculated by WorkerTime/ActualTime
    This is an interesting value since it can be a larger than 100% due to concurrent usages. Value of less than 80% means that the server is pretty idle, while a value that is higher than 2000% means that the server is extra busy

  1. ServerName – Name of server
  2. Sessions – Number of current live sessions
  3. SessionsRunning – Number of live running sessions that are currently executing operations
  4. SessionsIdle – Number of live sessions that are idle.
    In most cases, idle sessions are caused by open connections or connection pool that was not configured properly. If SessionIdle is too high it will deteriorate the performance
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. SessionsWait – Number of live sessions that are waiting
    Wait time is when OS is waiting for an object / database / server / OS / hardware resource

  1. ServerName – Name of server
  2. SessionType – Type of session
    Types can be SSMS, Visual Studio, .Net  etc.
  3. Information – additional information to session type
  4. Sessions – Number of live sessions
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. Processes – Number of host processes
    Number of machine host processes that exist
  7. ElapsedTime – Amount of connection time
  8. WorkerTime – The total time of running CPU operations since the system started
  9. MaximumCompletionTime – Maximum time of tasks completion
    SQL estimates the completion time of operation for some of the SessionTypes (Rollback, Recovery, Restore & Backup). This parameter holds this completion time, if any operation is currently ongoing

  1. ServerName – Name of server
  2. CounterType – Command type (Select, Insert, Update, Delete)
  3. Information – Additional information to counter type
  4. Sessions – Number of live sessions
  5. SessionsLock – Number of live sessions that are currently locking
    A session is considered locking if an object is locked for more than 10 seconds
  6. Executions – Number of total executions of each command
  7. ElapsedTime – Total time for command processing
  8. WaitTime – Total wait time
    Equals (ElapsedTime-WorkerTime). The time that the system is running without using the CPU
  9. WorkerTime – The total time of running CPU operations since the system started
  10. AverageWorkerTime – Average working time per execution
  11. WorkerTimePercentage – Ratio of worker time of command type and total server worker time
  12. PhysicalReadsPercentage – Percentage of physical reads from database to physical reads from the server
  13. LogicalReadsPercentage – Percentage of logical reads from database to logical writes from the server
  14. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server

  1. ServerName – Name of server
  2. TargetDatabaseName – Name of database (only special databases)
  3. Statement – Commands that were executed
  4. Sessions – Number of live sessions
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. Executions – Total number of executions
  7. LastExecutionTime – Last execution time
  8. ElapsedTime – Amount of operation time since start
  9. WaitTime – Amount of wait time
    Wait time is calculated as (ElapsedTime-WorkerTime). It means the amount of time in which running with no CPU work
  10. WorkerTime – The total time of running CPU operations since the system started
  11. AverageWorkerTime – Average working time
  12. WorkerTimePercentage – Precentage of database working time from server’s working time
  13. PhysicalReadsPercentage – Precentage of physical reads from database to physical reads from the server
  14. LogicalReadsPercentage – Percentage of logical reads from database to logical reads from the server
  15. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server

  1. TargetDatabaseName – Name of target database
  2. BufferSize – Size of data pages that are stored in buffer pool
  3. BufferSizePercentage – Percentage of buffer size of database relatively to servers’s buffer size
    If this the main database, it should consume 80% of the server. Main OLTP Database should consume above 80% of the server (unless resource and temp buffer is high)
  4. DataFiles – Number of database data files
    Files are gathered in file groups
  5. DataSize – Total database size
    We assume the size of a small database is 8 GB, small-medium database is 64 GB, 256 GB medium, 512 GB medium-large, 1+ TB large
  6. DataFreeAllocated – Total free already allocated data size
    The amount of memory that was allocated, and currently not in use (it can be recalculated using shrink command) due to: initial extent high, dropped objects, deleted data
  7. DataFreeUnallocated – Total free data size that is not allocated
    This is the storage size to which the database can grow
  8. DataStallTime – Total IO stall time
    If stall time is too high (relativly to wait and live time) there might be an IO/network storage or latch issue
  9. LogSize – Total size of log file
    A big log file means running in full recovery mode, and logs are not backed up often enough.
  10. LogFreeAllocated – Total free already allocated log size
  11. LogStallTime – Total IO stall time to log file
    If log stall time is high (relative to wait and live time) there might be an IO/network storage or latch issue
  12. CPURecommended – Recommended number of CPUs for the server based on data used.
    Data size of up to 4GB – 1 CPU
    Data size of 4-8GB – 2 CPUs
    Data size of 8-64GB – 4 CPUs
    Data size of 64-256GB – 8 CPUs
    Data size of 256-512GB – 12 CPUs
    Data size of 512-1024GB – 16 CPUs
    Data size of more than 1TB – 24 CPUs
  13. MemoryRecommended – Recommended server’s memory based on data used.
    Data size of up to 8GB – memory of 2GB
    Data size of 8-16GB – memory of 4GB
    Data size of 16-128GB – memory of 8GB
    Data size of 128-512GB – momory of 16GB
    Data size of 521-1024GB – memory of 24GB
    Data size of more than 1TB – memory of 32GB

  1. TargetDatabaseName – Name of target database
  2. CacheHitRatio – Ratio between amount of data that is retrieved from buffer pool to the total amount of data retrieved (from buffer pool and from IO requests)
    For OLTP, value should not be less than 80%
  3. ElapsedTime – The total elapsed time by that database operations (stored procedures mostly) since the server started
  4. WaitTime – The total wait time by the database. WaitTime = (ElapsedTime-WorkerTime)
    The time in which running without using the CPU since starting
  5. WorkerTime – The total time of running CPU operations since the system started
  6. WorkerTimePercentage – The worker time percentage of the database relatively to the server
    Main OLTP Database should consume above 60% of the server
  7. PhysicalReadsPercentage – The ratio between physical reads at the database to the server’s amount of physical reads
    This parameters should be close to the worker time percentage. If it is lower, it means that there are other batch processing in the server or ad hoc queries
  8. LogicalReadsPercentage – The ratio between logical reads at the database to the server’s amount of logical reads
    If it is lower, it means that there are other batch processing in the server or ad hoc queries
  9. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server. This value should be close to the worker time percentage. If it is lower it means that there are other batch processing in the server or ad hoc queries

  1. TargetDatabaseName – Name of target database
  2. Sessions – Number of live sessions
    If this parameter is much lower than server’s sessions, it means that something else is running
  3. SessionsRunning – Number of live running sessions currently executing operations
  4. SessionsIdle – Number of live sessions that are idle
    In most cases, idle sessions are caused by open connections or connection pool that was not configured properly. If SessionIdle is too high it will deteriorate the performance
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. SessionsWait – Number of live sessions that are waiting
    Wait time is when OS is waiting for an object / database / server / OS / hardware resource

The following statistics will be displayed for each of the tables in the database:

  1. SchemaName – Name of schema
  2. TableName – Namw of table
  3. TableRows – Number of rows
  4. TableStatus – Status of table: lock/empty
    Table is considered locked only if it is locked for more than 10 seconds.
    Empty tables are sorted last
  5. TableSize – Total size of table
  6. IndexSize-  Total size of indexes
  7. TableBufferSize – Total table size in buffer
  8. IndexBufferSize – Total index size in buffer
  9. BufferSizePercentage – Buffer percentage from server Table+Index buffer size from server
  10. AverageRecordSize – Average record size.
    Average number of bytes per record. If it is higher than 512 bytes, it means the row set is very large.
  11. AveragePageUsage – Average data page usage internal fragmentation.
    Higher the value, the better. If have fragmentation and have many indexes, consider rebuild/reorg indexes.
  12. AverageFragmentation – Average data page fragmentation external fragmentation.
    The lower this value, the better. If have fragmentation and have many indexes, consider rebuild/reorg indexes.
  13. IdentityUsage  – Usage of identity to maximum value (smallint/int/bigint)
    how much space to reach maximum value. How is it related to the table?
  14. UpdateFrequency – Update frequency.
    Update frequency of 100% means that the table is insert-only, 50% means the table is 100% updates. A value that is lower than 10% means that the table is queried often. Less than 1% means the table is static
  15. LastUpdateTime – Most recent time and date that the table was updated with one of the operations of: insert, update or delete
  16. LastQueryTime – Date and time of the most recent query
  17. LastAlterTime – Date and time of the most recent alter
  18. LastStatisticsTime – Date and time of most recent statistics gathering
    If this parameter is more than a month ago, it may be required to add a job

  1. TargetDatabaseName – Name of target database
  2. CounterType – Command type (Select, Insert, Update, Delete)
  3. Information – Additional information to counter type
  4. Sessions – Number of live sessions
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. Executions – Total number of executions of each command
  7. ElapsedTime – Total time for command processing
  8. WaitTime – Total wait time
    Equals (ElapsedTime-WorkerTime). The time that the system is running without using the CPU
  9. WorkerTime – The total time of running CPU operations since the system started
  10. AverageWorkerTime – Average working time
  11. WorkerTimePercentage – The worker time percentage of the database relatively to the server.
  12. PhysicalReadsPercentage – Percentage of physical reads from database to physical reads from the server
  13. LogicalReadsPercentage – Percentage of logical writes from database to logical writes from the server
  14. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server

  1. TargetDatabaseName – Name of target database
  2. SchemaName – Name of schema
  3. ProcedureName – Name of procedure
  4. Sessions – Number of live sessions
  5. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  6. Executions – Total number of executions.
  7. LastExecutionTime – Last execution time
  8. ElapsedTime – Total operation time since the server start.
  9. WaitTime – Total wait time.
    Wait time is calculated as (ElapsedTime-WorkerTime). It means the amount of time in which running without using the CPU.
  10. WorkerTime – The total time of running CPU operations since the system started
  11. AverageWorkerTime – Average working time
  12. WorkerTimePercentage – The worker time percentage of the database relatively to the server
  13. PhysicalReadsPercentage – Percentage of physical reads from database to physical reads from the server
  14. LogicalReadsPercentage – Percentage of logical writes from database to logical writes from the server
  15. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server

The following data will be displayed for each of the statements:

  1. TargetDatabaseName – Name of target database
  2. SchemaName – Name of schema
  3. ProcedureName – Name of procedure that was excecuted
  4. Statement – queries that are part of this procedure
  5. Sessions – Number of live sessions
  6. SessionsLock – Number of live locking sessions
    Session is considered locking if an object is locked for more than 10 seconds
  7. Executions – Total number of executions
  8. LastExecutionTime – Last execution time
  9. ElapsedTime – Total operation time since the server start
  10. WaitTime – Total wait time
    Equals (ElapsedTime-WorkerTime). The time that the system is running without using the CPU
  11. WorkerTime – The total time of running CPU operations since the system started
  12. AverageWorkerTime – Average working time per execution
  13. WorkerTimePercentage – Ratio between database working time and server time working
  14. PhysicalReadsPercentage – Percentage of physical reads from database to physical reads from the server
  15. LogicalReadsPercentage – Percentage of logical writes from database to logical writes from the server
  16. LogicalWritesPercentage – Percentage of logical writes from database to logical writes from the server

  1. TargetDatabaseName – Name of target database
  2. JobName – Name of job
  3. StepName – Additional name or description of job
  4. LastAlterTime – Date and time of the most recent alter
  5. LastExecutionTime – Last job execution time
  6. Executions – Total number of executions
  7. FailedExecutions – Number of failed or cancelled executions
  8. SucceededExecutions – Number of successfully or retry executions
  9. SucceededElapsedTime – Total successfully execution time
  10. AverageSucceededElapsedTime – Average successfully execution time

      1. TargetDatabaseName – Name of target database
      2. SchemaName – Name of schema
      3. ObjectType – Object Type such as table, synonym, trigger, view etc.
      4. Objects – Total number of objects of that type
      5. SystemObjects  – Total number of system objects of that type
      6. DetailObjects – Additional statistics about the objects (no total for this column). Each object has different attributes, and therefore, different content in this field, as follows:
        • Tables
          • Number of tables with identity column
          • Number of tables with rowversion column
          • Number of tables with lob. Data lob = text, image, xml, varchar(max), varbinary(max). Kept on different space
          • Number of tables with partitioning
          • Number of tables with clustered index
          • Number of tables with no primary key
          • Number of tables with no key no identity no primary key no unique not null
        • Triggers
          • Number of triggers set as instead of
          • Numbr of triggers set as after/for
          • Number of triggers set as DDL does the database has DDL triggers
          • Number of triggers disabled
        • Views
          • Number of views referencing remote server Referencing remote server/database can cause run-time compilation issues
          • Number of views referencing remote database Referencing remote server/database can cause run-time compilation issues
        • Synonyms
          • Number of synonyms referencing remote server Referencing remote server/database can cause run-time compilation issues
          • Number of synonyms referencing remote database Referencing remote server/database can cause run-time compilation issues
        • Functions
          • Number of functions scalar
          • Number of functions table-valued
          • Number of functions inline table-valued
          • Number of functions referencing remote server Referencing remote server/database can cause run-time compilation issues
          • Number of functions referencing remote database Referencing remote server/database can cause run-time compilation issues
        • Procedures
          • Number of procedures referencing remote server Referencing remote server/database can cause run-time compilation issues
          • Number of procedures referencing remote database Referencing remote server/database can cause run-time compilation issues
        • CLR Functions
          • Number of CLR functions scalar
          • Number of CLR functions table-valued
          • Number of CLR functions aggregate

The parameters to pass to PdbanalyzeDatabase are listed below:

Parameter Name Description Mandatory
TargetDatabaseName Name of target database Yes
FormatMode Format to display  results:
-1 is Story mode (text), 3 is full grid (default). 0, 1 and 2 slightly vary in their results view
No
TargetLinkedServerName Name of target server No
IncludeGrids Categories to include in the output No
ExcludeGrids Categories to exclude ffrom the output No

To use analyse option and other PartitionDB add-ins, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.