It's 2014. SQL Server 2000 is 14 years old, but there are still quite of you managing instances. SQL Server 2005 is 9 years old, and I'm sure more of you still deal with that version. I know because I work for a software vendor and I'm constantly asked if our software will run against those two versions of SQL Server.
For many of you, however, if you're managing a SQL Server 2000 instance, it might only be 9 or 10 years old. Your company might still have been installing SQL Server 2000 in the year 2005. The same is true for SQL Server 2005. I wouldn't be surprised to find companies still installing 2005 instances in 2008 or even 2009.
Companies don't care much about versions. They tend to mostly care about databases getting the job done, and sometimes, support. Many organizations don't see value in upgrading too often because of the overhead. I suspect many managers would prefer to get many years usage out of a platform before they change in order to minimize work that doesn't add value to their business.
The question this week asks you about the longevity of your database instances. Think about the average instance, or even the majority of your applications and how long they will remain on a particular version.
How many years will you run a platform before you upgrade it?
Years ago I heard someone at a large Fortune 100 company say their stated policy was to get 10 years of service out of a database server. At the time I thought that was a long time, but the more I think about it, the more I think that might be a minimum amount of time I'd want from a platform.
Let us know this week what you experience, and perhaps what you'd prefer.
Originally one of the articles in the first DBA Team series, Grant wonders what Raymond Chandler or Dashiell Hammett would have done if asked to write technical articles for Simple-Talk. He came up with the DBA detective, hard-boiled Joe Dee Beay. More »
SQL Saturday will come to Atlanta on May 3, 2014. This is a free, full-day event of SQL Server training and networking. SQL Saturday #285 also has 3 (paid for) pre-con sessions available run by Kalen Delaney, Teo Lachev, and Denny Cherry. Make sure to register for SQL Saturday while spaces are available. More »
Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.
Given the query below, what result will be returned when you execute the query?
declare @message varchar(15)
set @message = 'Query completed'
print IsNull(@message, 'Query completed with errors')
Answer: Query completed
The return type of IsNull function is the same as the check_expression. In this case varchar(15). The message 'Query completed with errors' is truncated to a length of 15, so only 'Query completed' remains.
I've always been wary about dealing with indexes and indexing strategies. As I learn more and more about the subject I realize how little I actually know. On the surface indexing can appear quite simple and straightforward. But the reality is that indexing requires a great deal of knowledge, can differ from environment to environment, and is basically an art form.
A while back I was tasked with investigating performance issues within an application driven by a SQL Server database solution. It ended up coming down to improper indexing, but in the course of the investigation I started to notice other, unrelated, indexing issues. As I dug further I needed more and more data to properly assess each and every index in the database (I won't go into further details but the design was a disaster to say the least). By the time I had finished my investigation and applied my fixes I was left with a fairly robust script that lists out many details regarding each and every index contained within a database.
Below is a listing of all the fields in the output. It's somewhat overwhelming at first, but depending on what one column's output is will determine what additional columns you'll likely want to look at. I will try to explain how I evaluate the data though I won't go through all of the columns nor will I go into any extensive details as you should already have a decent understanding of indexes if you're going to be using the script for any type of troubleshooting and / or architectural restructuring.
NOTE:I do not claim to be an indexing expert in any way, shape, or form. I simply want to make available to others what I've learned as well as to share some code which might come in handy for individuals who find themselves in a situation similar to the one I experienced. Also, be very careful when playing around with indexes. What seems like a good idea / change could have an extremely negative impact!
object_type: type of object (will either be "U" for User-Defined Table or "V" for View)
database_name: name of the database
schema_name: name of the schema
object_name: name of the object
create_date: date the object was created (time portion truncated, meant to give a general idea of how long ago the object was created)
modify_date: date the object was last modified (time portion truncated, will contain ".................." if it is the same date as the create_date column)
rows: total rows of data contained within the object
total_mb: total size of the object in megabytes
used_mb: total space used in megabytes
unused_mb: space reserved but not yet allocated in megabytes
data_mb: space used by data in megabytes
index_mb: space used by indexes in megabytes
pct_data: percentage of space used which is allocated to data
pct_index: percentage of space used which is allocated to indexes
index_type: type of index (Clustered, Nonclustered, Heap)
index_name: name of the index
system_named: indicates if the index name was generated by SQL Server or explicitly named (applicable to PKs and Unique Constraints / Indexes only)
is_pk: indicates if the index is a primary key
unique: indicates if the index is unique and whether its uniqueness if enforced by the index definition (displayed as "I") or a constraint (displayed as "C")
disabled: indicates if the index is disabled
hypothetical: indicates if the index is hypothetical
total_columns: number of columns which comprise the entire object
index_columns: number of columns which comprise the index key
include_columns: number of columns which comprise the include key
index_pct_of_columns: percentage of columns that make up the index as compared to the entire object
include_pct_of_columns: percentage of columns that make up the include as compared to the entire object
total_pct_of_columns: percentage of columns that make up the index + include as compared to the entire object
key_mb: space used by this particular index in megabytes
key_mb_pct: percentage of space used as compared to total space allocated to all indexes on the object
max_key_size_bytes: maximum possible byte size of the index based on the data size(s) of the column(s) involved
over_key_size_limit: indicates if the index key size exceeds the 900 byte limit
index_key: list of columns which comprise the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
include_key: list of columns which comprise the include portion of the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
filter_definition: filtered index definition / criteria
dupe_id: Indicates if the index is a duplicate (assigns a random "dupe group" ID and shows a count of total times the index is duplicated)
unused: indicates if the index is unused and therefore can potentially be dropped (does not look at PKs, Indexed FKs, Clustered Indexes, Unique Indexes / Constraints)
statistics_date: date when statistics were last updated on the index (blank for Heaps)
row_locks: indicates if the index allows row locks
page_locks: indicates if the index allows page locks
ignore_dupes: determines the error response when an insert operation attempts to insert duplicate key values into a unique index
auto_stats: indicates if statistics are automatically recomputed
padded: indicates if index padding is used
fill_factor: percentage of space to use when the index is created / rebuilt
user_seeks: total seeks by user queries
user_scans: total scans by user queries
user_lookups: total bookmark lookups by user queries
user_updates: total updates by user queries
last_user_seek: date of last user seek (time portion truncated)
last_user_scan: date of last user scan (time portion truncated)
last_user_lookup: date of last user lookup (time portion truncated)
last_user_update: date of last user update (time portion truncated)
system_seeks: total seeks by system queries
system_scans: total scans by system queries
system_lookups: total bookmark lookups by system queries
system_updates: total updates by system queries
last_system_seek: date of last system seek (time portion truncated)
last_system_scan: date of last system scan (time portion truncated)
last_system_lookup: date of last system lookup (time portion truncated)
last_system_update: date of last system update (time portion truncated)
Some Things To Keep In Mind / Look For Right Off The Bat:
Check the dupe_id column first, this is the easiest way to find low-hanging fruit
Check the unused column, but keep in mind how often the data is accessed and how long the instance has been online before deciding to drop any indexes
Make sure your statistics are up to date as this plays a large roll on how and when indexes are (or not) used
Compare how much of the object's space is used by indexes, and if it seems overly large consider dropping indexes which are rarely used and / or do little other than take up space
Check to make sure your index does not exceed the maximum index key size (see the over_key_size_limit column for this)
Even if an index is used, consider looking at the number of user_scans, user_seeks, etc. columns (if it is low then the index is likely not utilized that often and you are really just incurring a penalty to maintain it)
If an index is used, check to make sure it's not simply being used by the system for things such as updating statistics (check the system_scans, system_seeks, etc. columns)
Some Final Notes On The Script:
Certain rows of the output will have duplicate information suppressed for easier readability
The script does not include Spatial Indexes, XML Indexes, etc. (only Heaps, Clustered Indexes, and Non-Clustered Indexes), and some corresponding data values (such as size details) are calculated on these attributes only
Percentage breakdowns may not roll up to exactly 100% due to rounding issues
Certain stats (such as seeks / scans) on indexed views may not be what you'd expect (this will depend on how you query the object, for example whether you use WITH (NOEXPAND) or not on indexed views)
If you wish to run this on SQL Server 2005 you will need to comment out any code which references filter_definition
- Hi everybody
I'm studying Sequences and I've found something strange using them in the creation of a table. I created sequence...
CASE STATEMENT THE DOES A LOOKUP
- is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS...
Restrict Implicit Conversion.
- Hi All,
Create Table Test01(
1. Insert into test01 (business_date) values ('2013may6')
2. Insert into test01 (business_date) values ('20140409')
In the above example both the statement will succeed. But as per our requirement, the first statement...
Arithmetic overflow error
I have this code below.
create table dbo.Test(Base_fee [numeric](9, 6) NULL)
insert into dbo.Test(Base_fee) values (444444444.666666)
when I run it I get Arithmetic...
Why is remote scan
- I run the following statement in my local machine:
[color=#Blue]select top 10 * from sys.dm_os_performance_counters;[/color]
and I find the execute plan is [b]Remote...
Referential Integrity and Optimize
I have these tables:
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](max) NOT NULL,
[Qty] [int] NULL
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1)...
I have this table:
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](max) NOT NULL,
[Qty] [int] NULL
and I want to ALTER the...
Update in outer Apply
- Hi All,
Is it possible to put an update query within a outer apply?
[i]DECLARE @RemainingStock INT = 0
FROM OrderItems_tbl AS OI
Backup strategy to choose
- hello friends,
i have a sql server 2008 standard edition installed on a virtual server. This is our production Data warehouse...
Issue with nested SQL query
- Hi Team,
I am getting the following error message when I am trying to execute this nested query
"Incorrect syntax near 'Hours'."
Eliminating duplicates while insert
WITH cte_OrderProjectType AS
select Orderid, min(TypeID) , min(CTType) , MIN(Area)
from tableA A inner join
tableB B ON A.PID = B.PID left join
Assigning Batchid to Cake Making Process
- Hi ,
Please observe below records of table Bakery . When cake is order it flows from BaseMaking----------> Decoration------------> Delivered/Reject . If cake...
Need HELP with using IF or CASE in Where clause..
- Basically, I'm trying to do the below...
DECLARE @period VARCHAR(10)
SET @period = 'current'
select * from t_rbt_data
where dt between '2014-01-01' and '2014-03-31'
AND toy_type = 'robot'
- After seeing this: [url]http://www.sqlservercentral.com/Forums/Topic1546354-391-1.aspx[/url]
I'd like to see some articles that look at real situations where you've replaced a cursor (or...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.