Off the shelf software and installation prerequisites

Companies purchase off the shelf  software to cover all aspects of the business. I’m including: Payroll, Web Filtering, Security audits,Monitoring, Bookeeping, modelling .. the list is endless. Many of these products are dependant on databases.  

The typical situation is a list of instructions on how to make environment changes, security changes , DDL to create the database with objects.

The Installation Prerequiste needs a special mention. The vendor installation guide  has information relevant to the database creation. I’m amazed by some of the recommendations. Some examples recently:

1)       Autogrowth of data and log files at 50% . Read more on SQL LOG FILE AUTOGROW performance troubleshooting

2)       DBCC SHRINKDATABASE every night

3)       Logon mapped to database user is set with sysadmin privileges

 None of the 3 examples  fit into any  best practises . At this point I contact the vendor , explain the situation and they normally agree but with a caveat “they’ll take no responsibility if there is a product issue”.

Why does this situation arise?

 Most vendors release products on certain assumptions

1)       There is no expert DBA or IT support in the customer company

2)       Expectation of system to run unattended

3)       No knowledge of customer network

4)       No knowledge of customer hardware

A quality DBA will identify problems ,  often associated with background tasks or maintenance procedures. They will phone the vendor , who will recommend some adjustments

It should be a minimum requirement for all vendors to supply a detailed FAQ for DBAs to manage the system. The in-house DBA is likely to have a standardised approach customised to the system.  Integrating off the shelf software causes unnecessary overhead if every recommendation is followed

In some cases the recommendations are justified and explained in full, but that’s an exception , not the standard level of documentation.

Read More

SQL Server Performance Killers - SQL Server DBA

Write everything down during problem ... - SQL Server DBA

SQL Server – DBA tactics

SQL Server - How much resource is a SQL database using - memory, disk, IOPS,CPU

How much resource is each SQL database using?. There are different types of resource on the database server. Memory , disk,  IOPS, CPU.

The main difficulty with defining the resource usage for a specific database is the wide impact of one query across multiple databases. For example a query with a SORT may spill into the TempDB.

Other examples , are joins and transactions across more than one database

 These links to post showing techniques to isolate different resource usage. Analysing the resource usage from different angles , should give you a rough idea of usage .  Use this information to make decisions such as – add another database ? or move a database ? or add more resource to the server.

 If you’re  tuning – tuning at the database level can be frustrating – which is why it’s more effective at the query level .

 Memory 

 SQL Server – Calculate Database memory usage with sys.dm_os_buffer_descriptors  

Database Size – extra disk space

SQL Server Database Size and Free Disk Space ... - SQL Server

IOPS 

Measure current read & writes . Do the server drives have capacity?

 SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

CPU Utilization

 This one is difficult – I’ve achieved the best results  using the DMV - sys.dm_exec_query_stats . Read more on SQL Server – Find high impact queries with sys.dm_exec_query_stats  

 

 

SQL Server – Get sql table size and last updated for all tables

Part of the DBA job is Capacity Planning. Understanding future requirements is built into the database management process. Although future requirements are important to understand , maintaining the current database objects is important .One of the common tasks is creating reports presenting details about table sizes , row counts and usage. A regular audit of the database can discover small problems , maintaining good SQL Server performance

The DBA can work with the owner in understanding about table usage and index usage . Typical problems to solve are:

1)       Should data be archived? If time based data is not required , can it be moved to a different partition?

2)       Should a non-clustered index be dropped ? The Non Clustered Index may have been included , but the queries are no longer relevant. Maintaining the index adds an overhead – for a data updates and regular index management tasks.

3)       Are there application tasks creating Temporary tables , which are not cleared up?

 Any database server instance has  specific data requirements, presenting the information effectively to a non-DBA is a good starting point in understanding the problems and looking for solutions.

 Run this script on a database to present data for every index\heap in every table on a database, including row number, data space usage, percent of database, last user seek, last user scan, last user lookup.

I usually place this information in an Excel spreadsheet and work with the owner to analyse

 

use mydb
go
------------------SCRIPT ----------START-------------------------

-- Create the temp table for further querying

CREATE TABLE #temp(

tbl_id int IDENTITY (1, 1),

tbl_name varchar(128),

rows_num int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(rows_num, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set tbl_name = '?' where tbl_id = (select max(tbl_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100;

-- Get the data
--CTE : basic index information for every index
--join CTE output with the table size information
WITH temp_table(tbl_name,idx_name,last_user_update,user_updates,last_user_seek,last_user_scan,last_user_lookup,user_seeks,user_scans,user_lookups)
AS(
SELECT 
'[' + schema_name(tbl.schema_id) + '].['+object_name(ius.object_id)+']'
,six.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius 
INNER JOIN sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
INNER JOIN sys.indexes six ON six.index_id = ius.index_id and six.object_id = tbl.OBJECT_ID
WHERE ius.database_id = DB_ID()

)
select t1.tbl_name,t2.idx_name,t1.rows_num,t1.data_space,t1.index_space,t1.total_size,t1.percent_of_db,t1.db_size,
t2.last_user_update,t2.user_updates,t2.last_user_seek,t2.last_user_scan,t2.last_user_lookup,t2.user_seeks,t2.user_scans,t2.user_lookups from 
#temp t1 LEFT JOIN temp_table t2 ON t1.tbl_name = t2.tbl_name
ORDER BY t1.total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

--------------------SCRIPT FINISH----------------------------------------------

 Read More

 SQL Server - Capacity Planning

7 habits of highly effective DBAs - SQL Server DBA

Calculate table size with existing data - SQL Server DBA

SQL Server - How to find the largest sql index and table size - SQL ...

SQL Server - How to find the largest sql index and table size

Question: How can I find the largest sql table or sql  index in a database? I also want to report on the size of indexes and tables?

Answer:  Here are two different ways . Method 1 is more flexible – and you can build into reporting and capacity planning. Method 2 utilises the SQL Server Standard Report

Method 1

use myDB
GO
CREATE TABLE #TableSpaceUsed

(
           
           Table_name NVARCHAR(255),
           Table_rows INT,
           Reserved_KB VARCHAR(20),
           Data_KB VARCHAR(20),
           Index_Size_KB VARCHAR(20),
           Unused_KB VARCHAR(20)

)

INSERT INTO #TableSpaceUsed

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT Table_name,Table_Rows,
CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB, 
CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as dataKB, 
CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as reservedKB, 
CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB
FROM #TableSpaceUsed
ORDER BY dataKB DESC
DROP TABLE #TableSpaceUsed

 

Method 2: Use the Disk Usage by Tables Report  .

 

To use: Right Click on Database on SSMS > Reports > Standard Reports > Disk Usage by Table

 

SQL Server - TempDB performance and strategy checklist

TEMPDB performance is critical to SQL Server performance.  There are different uses for the SQL Server TEMPDB , and it's important to document the planned SQL activity.  I've seen many SQL Server Instances performance increase just by optimizing the TEMPDB

When thinking and planning TEMPDB  configuration  consider  TempDB purpose and how to maximise TEMPDB.

Different Uses of TEMPDB

User objects e.g tables, indices, table variables. Scope (user session or routine)

Internal objects  .e.g join work files Scope (statement)

Version Stores  to support row versioning

SQL Server Objects Using TEMPDB

Cursors

Databse Mail

DBCC CHECKDB

Index Rebuild with SORT_IN_TEMPDB

Queries

Temp tables

UDF

Version Store

Tactics for Optimizing TEMPDB

1) Create an IO profile of TEMPDB . Understand workload requirements.

2) Placement of TEMPDB .Locate the TEMPDB on a separate disk  to user databases.

3) Place the TEMPDB on the fastest IO subsystem possible in your environment.Read SQL Server – tempdb and solid state drives - SQL Server DBA

4) Sizing . Configure the TEMPDB  size  accurately particularly if performance decrease can’t be tolerated .

5) Recovery Model ,  keep it SIMPLE.

6) Set to Autogrow ,  see comment above re:Sizing. What rate of growth? TEMPDB grows raplidly , setting small growth rate may cause problems. Normally I set the auto-grow rate to 10% of the initial size. That does not mean the growth is set at 10% , it means that that if the initial size is 500 MB , the growth is set at 50 MB

7) Multiple data files for TEMPDB – 1 data file per CPU. In a tightly consolidated environment , 1  tempDB file per CPU may be a luxury.

8) Define the storage IO capability to the CPU processing capacity

9) Split TEMPDB into multiple files. Place the TEMPDB files on different drives. Depending on the disk alignment architecture – each drive is assigned to a separate LUN . If using SVC – there are different considerations. Speak to your SAN administrator about the storage architecture .

10) Configure the split TEMPDB  files as equal sizes.

Related Posts 

SQL Server – TEMPDB error - Could not allocate space for object dbo.SORT temporary run storage

Shrink tempdb without sql server restart

SQL server – 5 things SQL Server developers should know about SQL Server