A few things to keep in mind when deciding whether you want a unique index or constraint.
1) The index creation options other than FILLFACTOR that are available for a unique index are not available for a unique constraint.
2) A unique key can be referenced by a foreign key constraint but a column with a unique index cannot be referenced by a foreign key constraint.
3) A less subtle difference is related to the timing of validation. Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification. Often times indexes are validated at the end of a large modification. As a result it will take longer for a failure to occur with a rollback at the end of the modification.
Wednesday, June 16, 2010
SQL Formatter Is Great for Auto Generated SQL
Ever had to try to decipher SQL that was generated by a report writer or sql generation tool such as Cognos Framework Manager? How about trying to read through a co-worker's messy SQL? Not fun.
One of my favorite tools is the Instant SQL Formatter. It will clean up any valid SQL statement based on user configurable options. I usually just stick with the defaults. It's a great tool and it's free. Can't beat that. One of the first things I do when I set up a machine at a new client site is to add it to my favorites in IE.
One of my favorite tools is the Instant SQL Formatter. It will clean up any valid SQL statement based on user configurable options. I usually just stick with the defaults. It's a great tool and it's free. Can't beat that. One of the first things I do when I set up a machine at a new client site is to add it to my favorites in IE.
SSIS Extracting Reading from Oracle 10g
Here are a few hints if your extracting data from an Oracle database using SSIS.
1) Use Oracle 11g client. The 10g client has bugs around the naming of the Program Files directory on 64-bit machines. It doesn't like the parantheses in Program Files (x86). There are workarounds for using the 10g client but it's been my experience that it's not worth the effort.
2) Make sure to install the 32-bit client for running SSIS in the IDE (debug mode) and the 64-bit client for the 64-bit SSIS runtime.
3) Use the Attunity connector for Oracle. This component can extract data up to 100 times faster than out of the box OLE DB for Oracle provider.
1) Use Oracle 11g client. The 10g client has bugs around the naming of the Program Files directory on 64-bit machines. It doesn't like the parantheses in Program Files (x86). There are workarounds for using the 10g client but it's been my experience that it's not worth the effort.
2) Make sure to install the 32-bit client for running SSIS in the IDE (debug mode) and the 64-bit client for the 64-bit SSIS runtime.
3) Use the Attunity connector for Oracle. This component can extract data up to 100 times faster than out of the box OLE DB for Oracle provider.
Wednesday, May 12, 2010
How To: Shrink a Log File
Are you in a dev environment and by screwing around with large dml statements you've created a monsert log file that you need to truncate and are not worried about loss of data? Is your database in Simple Recovery Mode? If you answered yes to these questions . . . fire away:
DBCC SHRINKFILE (<Log Name>, 0, TRUNCATEONLY)
If you get an error stating that the log file is in use but you have an immediate need to truncate the log file and don't want to wait you can also do the following:
1) Detach the database
2) Delete the log file from the file system
3) Attach the database and remove the the reference to the log file prior to completing the process in the UI
Upon attach, SQL Server will create a new log file at the minimum size specified.
DBCC SHRINKFILE (<Log Name>, 0, TRUNCATEONLY)
If you get an error stating that the log file is in use but you have an immediate need to truncate the log file and don't want to wait you can also do the following:
1) Detach the database
2) Delete the log file from the file system
3) Attach the database and remove the the reference to the log file prior to completing the process in the UI
Upon attach, SQL Server will create a new log file at the minimum size specified.
Tuesday, April 13, 2010
Don't Forget to Clear Your Buffer!
Sounds like a Colon Cleanse pitch no?
Well it's not. One of the most important things you can do when performance tuning is to remember to clear the procedure cache between test cases.
Don't be fooled by the word procedure. The procedure cache deals with almost all queries submitted to the SQL Server engine, not just those that live in stored procedures. It stores compiled execution plans for later use which helps speed things up by not having to regenerate execution plans when one is already available.
Back to clearing . . .
It's important to clear the cache between query submissions so that we start from scratch when we submit our new query. If we have "left-over" plans in the cache the optimizer won't necessarily create a newly optimized plan thus corrupting your tuning/testing efforts.
DBCC FREEPROCCACHE will clear all execution plans from the cache causing all subsequent SQL statements, stored procs or not, to be recompiled the next time they run.
It's also helpful to run DBCC DROPCLEANBUFFERS to clear the data buffers. This will ensure more accurate testing as all queries will have to retrieve their data from disk. Running CHECKPOINT prior to DROPCLEANBUFFERS will move all dirty data pages to disk, even further ensuring accurate testing results.
http://www.devx.com/tips/Tip/14401
Well it's not. One of the most important things you can do when performance tuning is to remember to clear the procedure cache between test cases.
Don't be fooled by the word procedure. The procedure cache deals with almost all queries submitted to the SQL Server engine, not just those that live in stored procedures. It stores compiled execution plans for later use which helps speed things up by not having to regenerate execution plans when one is already available.
Back to clearing . . .
It's important to clear the cache between query submissions so that we start from scratch when we submit our new query. If we have "left-over" plans in the cache the optimizer won't necessarily create a newly optimized plan thus corrupting your tuning/testing efforts.
DBCC FREEPROCCACHE will clear all execution plans from the cache causing all subsequent SQL statements, stored procs or not, to be recompiled the next time they run.
It's also helpful to run DBCC DROPCLEANBUFFERS to clear the data buffers. This will ensure more accurate testing as all queries will have to retrieve their data from disk. Running CHECKPOINT prior to DROPCLEANBUFFERS will move all dirty data pages to disk, even further ensuring accurate testing results.
http://www.devx.com/tips/Tip/14401
Monday, March 29, 2010
Performance Issue: UNPIVOT with a Large Number of Columns
I love the PIVOT and UNPIVOT commands. They have saved me a ton of time on quite a few projects. UNPIVOT especially, since many times when working on BI projects I'm handed a monster spreadsheet by someone on the business side. Business people like seeing pivoted data. Database people like seing normalized data. I won't get into the keywords/commands here since there is plenty of material out there, most of which do a better job than I ever could of explaining their use.
However, I will tell you that SQL Server does not like "UNPIVOTing" a large number of columns as I recently found out. I should note that the query I have contains a few nested SELECTs (which I'm sure is part of the problem). I don't know the internals of what the database engine is doing when you using these commands but I do know they produce some pretty ugly execution plans. Since I just started this project and have a year of work to deliver in about 2 months (I'm not kidding) I haven't had the time to deconstruct the execution (nor should I really be writing about this topic).
What I have figured out is that if I break up a 280+ column UNPIVOT into 20 column chunks (an unpivot for every 20 columns - yes this is a maintenance nightmare) then I get reasonable performance (a couple of minutes to execute) whereas my original query never completed.
There's no way I'm going to be ok with having 15 queries instead of one to solve a performance issue but this is what I know for now and I'm able to present a working proof-of-concept. I hope to have a better understanding of the issue and as a result, the solution soon. I'll keep you posted. In the meantime, if you know what's going on please share . . .
However, I will tell you that SQL Server does not like "UNPIVOTing" a large number of columns as I recently found out. I should note that the query I have contains a few nested SELECTs (which I'm sure is part of the problem). I don't know the internals of what the database engine is doing when you using these commands but I do know they produce some pretty ugly execution plans. Since I just started this project and have a year of work to deliver in about 2 months (I'm not kidding) I haven't had the time to deconstruct the execution (nor should I really be writing about this topic).
What I have figured out is that if I break up a 280+ column UNPIVOT into 20 column chunks (an unpivot for every 20 columns - yes this is a maintenance nightmare) then I get reasonable performance (a couple of minutes to execute) whereas my original query never completed.
There's no way I'm going to be ok with having 15 queries instead of one to solve a performance issue but this is what I know for now and I'm able to present a working proof-of-concept. I hope to have a better understanding of the issue and as a result, the solution soon. I'll keep you posted. In the meantime, if you know what's going on please share . . .
Sunday, February 28, 2010
Clustered Index Doesn't Have to be the Primary Key
Believe it or not, you don’t have to cluster your primary keys.
Most SQL Server mortals assume that their primary keys should be clustered because that is the default behavior of SQL Server Management Studio when using the designer to create a table. Unfortunately, the primary key is not always the best candidate for the one and only clustered index you're allowed on a table.
I won't go into detail here about what a clustered index is but I'll say that it is essentially your table and the order in which your table rows/records/tuples are stored is based on the column(s) used in your clustered index.
In an OLTP database where your pimary keys consist of a single integer identity column you're usually in good shape if your clustered index is also your primary key.
However, if you use composite and/or "intelligent" keys you're going to experience a number of performance problems. I won't expound on these problems here because I almost never use intelligent or composite keys in an OLTP system and don't believe they should be used in such systems.
The place I most often have a non-primary key clustered index is in an OLAP fact table. OLAP systems are more concerned about high performance reads of blocks of data as opposed to reads and writes of individual rows as is the case in transactional systems.
In a data warehouse/OLAP database date is often an attribute used in most analyses. By creating a clustered index on the most important date in a fact table (i.e. order date in an order table), you enable better I/O and therefore faster query response times for the majority of queries executed.
Some might argue that the unique surrugate key of the fact table (if you even have one) should have a clustered index. Hmmm . . . how often do you query a fact table and have a surrogate key based predicate? The answer should probably be never (although I almost never say never)!
How about writes? Fact tables are often written to based on a date. By clustering on date we typically have sequential writes to the disk. When we load today's data we end up adding data to the end of the last data page for the table because today's date comes after yesterday's date. If we were to cluster on some kind of an intelligent key we'd end up having to insert rows into pre-existing data pages which leads to much higher I/O and therefore decreased performance.
How about archiving and partitioning? In most OLAP scenarios the business requirement is to have something like 25 months worth of data available in the system at all times. It's often beneficial for performance and disk expense reasons to take old data offline. The best way to do this in a SQL Server environment is to partition your fact tables using a year/month based partition function. For partitioning to be successful it is necessary for your partition column to be part of the clustered index. If you cluster on your date column and partition on your date column as well, you're all set.
In short, if you're working in a data warehouse/OLAP environment pay careful attention to what column(s) you create your clustered indexes on. It can make or break the performance of your solution!
Most SQL Server mortals assume that their primary keys should be clustered because that is the default behavior of SQL Server Management Studio when using the designer to create a table. Unfortunately, the primary key is not always the best candidate for the one and only clustered index you're allowed on a table.
I won't go into detail here about what a clustered index is but I'll say that it is essentially your table and the order in which your table rows/records/tuples are stored is based on the column(s) used in your clustered index.
In an OLTP database where your pimary keys consist of a single integer identity column you're usually in good shape if your clustered index is also your primary key.
However, if you use composite and/or "intelligent" keys you're going to experience a number of performance problems. I won't expound on these problems here because I almost never use intelligent or composite keys in an OLTP system and don't believe they should be used in such systems.
The place I most often have a non-primary key clustered index is in an OLAP fact table. OLAP systems are more concerned about high performance reads of blocks of data as opposed to reads and writes of individual rows as is the case in transactional systems.
In a data warehouse/OLAP database date is often an attribute used in most analyses. By creating a clustered index on the most important date in a fact table (i.e. order date in an order table), you enable better I/O and therefore faster query response times for the majority of queries executed.
Some might argue that the unique surrugate key of the fact table (if you even have one) should have a clustered index. Hmmm . . . how often do you query a fact table and have a surrogate key based predicate? The answer should probably be never (although I almost never say never)!
How about writes? Fact tables are often written to based on a date. By clustering on date we typically have sequential writes to the disk. When we load today's data we end up adding data to the end of the last data page for the table because today's date comes after yesterday's date. If we were to cluster on some kind of an intelligent key we'd end up having to insert rows into pre-existing data pages which leads to much higher I/O and therefore decreased performance.
How about archiving and partitioning? In most OLAP scenarios the business requirement is to have something like 25 months worth of data available in the system at all times. It's often beneficial for performance and disk expense reasons to take old data offline. The best way to do this in a SQL Server environment is to partition your fact tables using a year/month based partition function. For partitioning to be successful it is necessary for your partition column to be part of the clustered index. If you cluster on your date column and partition on your date column as well, you're all set.
In short, if you're working in a data warehouse/OLAP environment pay careful attention to what column(s) you create your clustered indexes on. It can make or break the performance of your solution!
Subscribe to:
Posts (Atom)