|
|
Recently Submitted Articles |
|
|
|
|
|
|
|
Recent SQL Clinic Articles |
|
|
|
|
|
|
|
|
 | | BEST PRACTICES FOR WRITING BETTER SQL
Author :: Mahmud Hasan
Date :: Tue 08/12/2008 @ 12:27 |
| PermalinkThe performance of SQL query that we write for different projects is a very crucial issue for us. In many cases we have to perform time consuming optimization work to come out from this performance related problems. Surprisingly, most of the time we succeed to improve our poor SQLs to an optimum level of acceptance. In my experience, when we worked on these optimizations we found, if we could follow some very simple practices of writing SQL and were a little more aware of performance, this issue could have been avoided from the beginning. So, our goal should be to write the best possible SQLs in our first attempt so that we do not need to spend our expensive time to optimize them later.
read article
BEST PRACTICES FOR WRITING BETTER SQL
Background:
The performance of SQL query that we write for different projects is a very crucial issue for us. In many cases we have to perform time consuming optimization work to come out from this performance related problems. Surprisingly, most of the time we succeed to improve our poor SQLs to an optimum level of acceptance. In my experience, when we worked on these optimizations we found, if we could follow some very simple practices of writing SQL and were a little more aware of performance, this issue could have been avoided from the beginning. So, our goal should be to write the best possible SQLs in our first attempt so that we do not need to spend our expensive time to optimize them later.
Here I am trying to share some of the experiences I gained while working in different optimization tasks.Analyzing SQL:
Analyzing the performance of your SQL at best you can in parallel to the development is very important. If you perform this analysis for every query/operational statement you write before moving to the next query/operational statement of the procedure you can easily trace where the performance is suffering. You should always remember, the time that your procedure takes is the summation of all the query and operational statement that executes inside it. Normally if any of your queries takes more than 0-20 millisecond, you must revise the SQL to see if there is better way to write it.Here I am providing one utility function that I have written and used in some of my optimization work and found very helpful for tracing the execution time of any SQL in millisecond level:
CREATE FUNCTION [dbo].[GetCurrentTime]()
RETURNS varchar(100)
AS
BEGIN
DECLARE @time varchar(100)SET @time = (CAST(DATEPART(mi,getDate()) AS varchar(50)) + ' Min: ' + CAST(DATEPART(ss,getDate()) AS varchar(50)) + ' Sec: ' + CAST(DATEPART(ms,getDate()) AS varchar(50)) + ' Ms')RETURN @time
END
You can use the above function in the following way:DECLARE @time varchar(100)
SET @time = ( dbo.GetCurrentTime())
print 'Current Time: ' + @time
PRINT '----------------------------------------------------------'--Here write your sql statement.PRINT '----------------------------------------------------------'
SET @time = ( dbo.GetCurrentTime())
print 'Current Time: ' + @time
This will print the execution start time and execution end time of your SQL in milliseconds while you run the SQL for tracing in the query analyzer. So, from this result you can easily find the time your procedure is taking to execute. Instead of using this function you can utilize SQL profiler as well. But this function will help you get the timing with in the same window that you are working in. However, SQL profiler provides few more information about your query that also might help you to analyze the performance of the SQL. So, do not keep it away from you.
Best Practices:
1. Database Design:
The Design of your database is very important for writing best performance SQL. You must ensure the database is normalized to some extent. Be sure that normalization does not affect the performance of your database. For large and frequently accessed database normalization can cause performance downfall in some cases. So, you must analyze the design carefully to decide the level of normalization you require.
2. Writing Trigger:
In many scenarios we write triggers on database tables. In one of my optimization task I found few triggers extremely slow in performance due to some conceptual lacking of the developer. So here I want to share my experience about those triggers.
Before writing a trigger we need to understand what trigger is and how exactly it works. Trigger is actually a procedure that runs in response of an event fired due to performing some operations on database tables. The events could be insert, update or delete. Now, the question is how database handles the execution of a trigger when it fires? If you write a trigger for insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in the memory. Then it performs the insert operation and after that the statements inside the trigger executes. We can query the “INSERTED” table to manipulate with the inserted row/s from the trigger. Similarly if you write a trigger for delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row. More importantly you must understand how an update trigger works. After firing an update trigger it works in the following sequence:
i. All constraints are enforced.
ii. All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
iii. The inserted and deleted tables are created for use within the trigger.
iv. The triggering action (in this case the UPDATE statement) is executed.
v. The AFTER UPDATE trigger executes.
From the above steps you can see that no table called “UPDATED” is created. Actually on database no operation called update executes. Internally it actually deletes the row to be updated and then inserts the row with the new updated value. So, from an update trigger we can access both INSERTED and DELETED table though directly we may not execute any insert or delete operation. This is a very important concept for us. Here I am providing the mistakes you may make if you are not clear on this.
Consider the following trigger:
CREATE TRIGGER TriggerName ON YourTableName
AFTER UPDATE ,INSERT , DELETE
AS
if(exists(select Contact_Id from inserted where Contact_id is not null))
begin
--Do your operation
endif(exists(select Contact_Id from deleted where Contact_id is not null))
begin
--Do your operation
end
Here the developer wrote the trigger for all the events on the table and expecting to do some operation if Contact_ID is inserted, deleted or updated in to the table. Now note carefully the mistakes that the developer did in this trigger. For example an operation is executed on the table which updates some other field other than Contact_ID. Now if Contact_ID is a not null column of the table we will never get null from INSERTED and the DELETED table. So, here even though Contact_ID is not updated the operation of the triggers will execute. It is not finished yet. It has more problems as well. The Developer wrote the 2nd if condition assuming that DELETED table will be created only when trigger fires for any Delete operation on the table. But you see, as a matter of fact this table is also available when the trigger fires for update operation. The situation will be the worst if the developer thinks the first if statement will be successful for INSERT and UPDATE operation and 2nd if statement will be successful for DELETE operation. Only In that case he might write the same instructions to execute both if statements. This will in turn execute the same operation twice. Doesn’t it sound very silly and surprising? Yes. But if you are not careful these mistakes can happen anytime and can take you to hell from the heaven you are currently in.
So, while writing a trigger keep an eye on the following points:
i. If you write a single trigger for multiple event, be very careful to ensure that your trigger does not execute for unwanted events.
ii. When writing update trigger always check if your desired column is updated by using IF UPDATE(ColumnName).
iii. Be very careful in querying INSERTED and DELETED table.
iv. Try to avoid cursor from the trigger.
v. Ensure that your trigger is not creating any deadlock/Infinite loop on your database.
3. Using INNER JOIN Vs WHERE clause:
This is true that from performance perspective a query written using WHERE clause and a query written using INNER JOIN do not have any difference. If we watch the execution plan of both the query we will see it actually executes in the same way. Then what actually you should use for joining 2 tables? You must always use INNER JOIN not WHERE clause. That means you have to ensure that if you have n number of tables in your query you must have at least n-1 number of joins. Why? Well, there are several reasons:
i. INNER JOIN makes your query more readable and logical looking.
ii. It makes your query more manageable.
iii. It separates the joining condition from filtering condition of your query.
iv. It will make your life easier when you have complex queries with several joins.
v. It will minimize the possibility of error in the result of the query
vi. Above all, trust me, it will reduce the time for writing the query.
4. Index Creation:
Creating necessary indexes on your database is very important. In one of my
optimization work I found just creating 2 indexes on a table gives my query 50% performance boost. Determining the correct indexing policies is very crucial for your database. You must remember that proper indexing is good for your database performance but on the other hand creating indexes unwisely can affect your database performance significantly. Unnecessary index creates overheads on database server memory and also could result extremely slow (Sometimes never ending) query performance. So what are the basics you should follow to consider creating index? Here are some key features that makes database columns to be candidate for indexing:
i. Ensure that you have at least one primary key in your table. Sql Server automatically indexes the primary keys.
ii. All foreign keys are good candidates to be indexed.
iii. Columns on which ordering operation is done very frequently are very good candidate for indexing.
iv. Columns on which a large table is filtered or joined in different queries are also good candidates for indexing.
Remember for creating index no rule can be considered as Bible. You should always analyze the database and go for extensive testing to find if an index is helpful for your database. For better understanding you may like to do further study on it.
5. Using Temporary Table:
In many stored procedures I have found we have used temporary tables without giving much thought on it. We should know that temporary tables are created in the tempdb and and any insert/update/delete operation or queering from temporary table has extra overhead as you have to access a table of out side of your database internally. So, try to avoid temporary table as long as you can. If you see there is no alternative of using this, you must define the table with CREATE TABLE statement with proper primary/unique key constraints and if required create indexes on it. This perhaps can improve the performance of your query.
6. Using Table Variable:
In SQL server we have an alternative of temporary table called Table variable. Perhaps many of you are not aware of this. Table variable is created in memory and performs lot better than temporary table reducing the overhead on database. But If you need a table that will contain huge amount of data temporary table actually could be better for you. Because you cannot create index on Table variable but in temporary table you can.7. Avoid Using Dynamic SQL:Think twice before writing any dynamic SQL in your procedure. If possible try to avoid this. Here are few drawbacks of writing dynamic SQL:
i. It does not allow your stored procedure to be pre compiled and thus affects performance.
ii. It makes your query less manageable.
iii. It makes your query less readable.
iv. It can open your procedure for possible XSS attack.
In many cases we can avoid writing dynamic SQL. For example, in most of the cases you write dynamic SQL for using variable with TOP key word. Yes you needed to do it in SQL 2000. But now in SQL 2005 it can be achieved without dynamic query. Just put a bracket around your variable with the TOP key word and it will work. Example: SELECT TOP (@size) marks FROM resultSo, the key point is you will not write dynamic query as long as it can be avoided.
8. Using Like Operator:
Filtering your query using Like key word could be very slow in performance if you query on a table with huge amount of data specially if you have other joins with this filtering criteria. So when you need to achieve the functionality of “Like” operator on a high volume table consider using Full Text query instead of that.
9. Using OR in your WHERE condition:
Be very careful in using OR in your filtering criteria’s. It can make your query significantly slow. In one of my optimization work I got a query of the following format:
FROM
Action
,Relationship rel
where
(Action.GROUP_ID=@GroupID
OR (rel.GROUP_ID=@GroupID
AND rel.CONTACT_ID= Action.CONTACT_ID
)
)
A query written in above format was taking almost 7-8 sec to execute. I just spited the query in 2 queries and united them using UNION operator. It improved the query to be executed just in few milliseconds. You see the significant performance gain you can achieve in this way? So always try to utilize UNION instead of using OR filtering criteria.
10. Use of cursor:
Probably cursor is the most dangerous tool exists in SQL that can kill the performance of your procedure. Remember in most of the cases cursors can be avoided by different kind of JOINS, sub queries etc. So think, analyze and if needed take help of others before writing any cursor in your procedure. In one of the optimization task that I worked on I improved the query performance more than 40% by removing cursor from the query.
11. Never Use SELECT * FROM TABLE:
Many of us have tendency of writing SQL using “SELECT * FROM TABLE” format. Do not do it. Even if you need all the columns to be returned, you specify all the columns by their name in your query. Moreover always ensure you are returning only those columns that you really need for your purpose – nothing more than that. This is actually a matter of habit that you can achieve just by not becoming lazy during writing SQL.
12. Print statement:
Sometimes you write lot of print statements to trace your procedure execution. Then after completing the procedure you do not delete those print statements. You should remember event for printing a single digit your processor needs some time and memory. It could be less then micro second but do not forget summation of microseconds become millisecond and summation of milliseconds become second. Yes I have gained almost 1 second performance gain just by deleting all the print statements in one of my optimization task.
13. Using SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
14. Using @@ERROR:
Make sure that the @@ERROR global variable is checked after every statement which causes an update to the database (INSERT, UPDATE, DELETE). Make sure that rollbacks (if appropriate) are performed prior to inserting rows into an exception table
15. Minimize using Not Equal operator:
Minimize the use of not equal operations, <> or !=. SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression. Try rephrasing the expression using ranges: WHERE KeyColumn < 'TestValue' AND KeyColumn > 'TestValue'
16. DONOT start the name of a stored procedure with SP_:
This is because all the system related stored procedures follow this convention. Hence a valid procedure today may clash with the naming convention of a system procedure that gets bundled with a Service pack / Security patch tomorrow. Hence do not follow this convention.
17. Using Indexed View:
Sometimes we would require an view to be indexed. This feature is bundled with SQL Server 2000. The result set of the indexed view is persist in the database and indexed for fast access. Because indexed views depend on base tables, you should create indexed views with SCHEMABINDING option to prevent the table or column modification that would invalidate the view. Hence using them can reduce a lot of load on the base tables but increases the maintainability.
18. Readability:
i. Ensure the readability of your SQL.
ii. Capitalize all key word like SELECT, WHERE etc.
iii. Use camel casing in naming the variables.
iv. Breakdown your SQL in multiple lines. Never write too long line that can become tough to trace. A very well indented query could be like the following:
SELECT
c.CONTACT_ID
,u.USER_ID
FROM
Cac_Contact c
INNER JOIN
Cac_User u
ON
c.CONTACT_ID = u.CONTACT_ID
End Words:
In this article I have tried to point out the core best practices that you should follow whenever you write any SQL. I have come to learn all of these throw experience rather than study documents or browsing internet. So, I hope this could be practical guide line for all of us in our development to ensure best performance SQL query. Above all you all should remember to ensure the best performance, testing during development has no alternative. So keep tracing your procedures in different scenarios and with different data load so that it can pass any future situation. Be accustomed of using SQL profiler to analyze your query and always keep an eye on the exaction plan that query analyzer generates. I believe above everything patience is the most important thing to improve your SQL’s performance. So don’t get hurry to meet your deadline, because it can cause expensive optimization work to come in front. Rather you take your time to write the best SQL, discuss with your team mates, managers if needed and ensure that you are writing the best possible implementation of your query.
|
| |
|
 | | Multiple INSERTS using Row Constructors in SQL Server 2008
Author :: Sirisha Kommushetty
Date :: Fri 08/08/2008 @ 02:01 |
| PermalinkRow Constructors in SQL Server 2008 are a relief to single ton manual inserts. They definitely help boost the efficiency of I-Transactions (INSERT). If you really want to second my statement J… then I bet you should have a quick look at the case study below.
read article
Row Constructors in SQL Server 2008 We all know the fact that ISUD transactions are the Crux of RDBMS/T-SQL and also how well and often the LOB(Line of Business) relies on ISUD and implements it in their business logic thereby performing thousands and millions of transactions a day(Especially, inserts and updates). Row Constructors is a really cool handy feature that comes to play with the ‘I’ part of ISUD transactions. That is, the INSERT transactions  . I personally feel that Row Constructors are a relief to single ton manual inserts. They definitely help boost the efficiency of I-Transactions (INSERT). If you really want to second my statement  … then I bet you should have a quick look at the case study below. The concept ‘Row Constructors in SQL Server 2008’ basically deals with the crux of multiple inserts performed at one shot. To get a brief picture of why we need multiple inserts and the advantage it has we firstly need to understand all possible ways of INSERTING Data into a Table The possible ways of conducting inserts to a table/ Inserting Data into a Table: By employing ‘INSERT INTO’ we can conduct Single ton Inserts at one shot and this could be repeated multiple times (depending on the data to be entered). Look at the following examples. Ex: Assume that we have an existing table named Car and we need to Insert some data specific to cars into it. CREATE TABLE Car ( CarID varchar(10) ,Name varchar(40) ,Color varchar(20) ,Brand varchar(20) ) INSERT INTO Car VALUES('AP10J4195','Swift','FerrariRed','Maruthi'); INSERT INTO Car VALUES('AP10P4184','800','White','Maruthi'); INSERT INTO Car VALUES('AP10K2340','CityZX','Grey','Honda'); INSERT INTO Car VALUES('AP10K2218','CityZX','BlackStatement','Honda'); INSERT INTO Car VALUES('AP10K2000','Mercedesc200','BlackStatement','Benz'); The first time I write my INSERT INTO VALUES… statement. And, then I I do a Copy paste several times and refill values and then do a Ctrl+E..  I need to recheck my data & statements here. Case(ii) : INSERT INTO EMPLOYING SELECT Similar to Case (i) we conduct single ton inserts multiple times. We end up writing a chunk of code L. Here again, I do an Alt+F1 : Copy- Paste Columns and build my INSERT Query  Example: Assume that we have an existing table called Car and we insert need to some data from Car into a new table ‘Vehicle’. (Create the Car Table same as above) CREATE TABLE Car ( CarID varchar(10) ,Name varchar(40) ,Color varchar(20) ,Brand varchar(20) ) INSERT INTO VEHICLE ( Name ,Color ,Brand ) SELECT Name , Color , Brand FROM [dbo].Car WHERE Brand = 'Honda' SELECT * FROM Vehicle This statement is used when a table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns. I don’t use this much may be because it doesn’t cover all possible cases . Generally, go for Case(ii). Examples: SELECT Name ,Color ,Brand INTO CarOfTheYear FROM [dbo].Car WHERE Name = 'Swift' SELECT * FROM CarOfTheYear Case (iv) : UNION ALL This has been a handy statement that developers generally use. Especially, as part of Data Insertion Scripts. Regarding performance there is not much difference. If there is performance difference it does not matter as this is generally used one time insert script. There is no need to do a copy - paste of Chunk of code and refill values (though people do it.. ). You can enter values employing subsequent SELECT’s and have focus on your tasks rather than re-checking it again and save time. INSERT INTO VEHICLE ( Name ,Color ,Brand ) SELECT 'Activa','BlackStatement','Honda' UNION ALL SELECT 'Pulsar', 'BlackStatement','Bajaj' UNION ALL SELECT 'I10','Silver','Hyundai' UNION ALL SELECT 'Swift','CementGrey','Maruthi' GO SELECT * FROM Vehicle DROP TABLE Car DROP TABLE Vehicle Case (v) : EMPLOYING ROW CONSTRUCTORS There is another way available in SQL Server 2008 called Row Constructor which is an ANSI terminology for pseudo table of rows.
It is actually a way to provide a set of row values in one statement. That is multiple Inserts in One Statement . (NOTE: Available only in SQL Server 2008) SYNTAX : (Col1 , Col2 … ) INSERT INTO
VALUES (a,b,..) Let us look at the following example to understand the usage. SAMPLE PLAY : Use TempDB GO ------------------------------------------ -- Create sample Tables ------------------------------------------ CREATE TABLE Car ( Name varchar(40) ,Color varchar(20) ,Brand varchar(20) ); GO CREATE TABLE Vehicle ( Name varchar(40) ,Color varchar(20) ,Brand varchar(20) ); GO ---------------------------------------- -- Usage : Multiple Inserts ---------------------------------------- INSERT INTO Car VALUES ('Swift','FerrariRed','Maruthi') ,('CityZX','Grey','Honda') ,('MercedesC200','BlackStatement','Benz') ,('CityZX','BlackStatement','Honda') ,('Aveo','Red','Chevrolet'); GO --SELECT * FROM Car I thoroughly enjoyed when building the query with Row Constructors as you just need to fill in values with no subsequent SELECT’s as in Case(iv)- Using Union ALL. Just need to specify one INSERT keyword and start filling values separated by comma. Absolutely, no need of copy-paste! Just can focus more on data to be entered. That’s all. Wanna have more fun.. Some more Usage…. a) Employing Scalar Sub queries to insert data using Row Constructors. ------------------------------------------------------ --INSERT using Scalar Sub-Queries ------------------------------------------------------ INSERT INTO [dbo].Vehicle VALUES ( (SELECT TOP(1) Name FROM [dbo].Car) , (SELECT TOP(1) Color FROM [dbo].Car) , (SELECT TOP(1) Brand FROM [dbo].Car) ) SELECT * FROM Vehicle b) Displaying Data – by building something on the fly ------------------------------------------------------------------------------------- --Using Row Constructors as a DataSource – On the Fly! ------------------------------------------------------------------------------------- SELECT * FROM ( VALUES ('I10','Silver','Hyundai',4) ,('Aveo','Black', 'Chevrolet',5) ) tab(Name,Color,Brand,RatingThisYear) This is amazing!! Now, that you’ve had a jump start on the ways to INSERT data. Do you second my statement on Row Constructors – “Row Constructors are a relief to those Single ton Inserts!” If so, are you okay with incorporating Row Constructors in your/our Data_Insertion scripts for tables  .
|
| |
|
| | Estimate Disk Space Needed for SQL Server Installation
Author :: Cindy Gross
Date :: Wed 07/16/2008 @ 04:40 |
| PermalinkIn addition to raw disk space you will want to consider number of physical disks, SAN vs. local disks (direct attached storage), RAID level, number of controllers, and number of arrays. Along with that is the decision on how to spread your data, indexes, and logs over the various arrays. This reply will concentrate purely on the overall disk size question. However, I do want to emphasize that it is VERY important to properly layout your files over multiple disks on the correct type of RAID and these things will affect the amount of disk space you need. [READ MORE]
read article
In addition to raw disk space you will want to consider number of physical disks, SAN vs. local disks (direct attached storage), RAID level, number of controllers, and number of arrays. Along with that is the decision on how to spread your data, indexes, and logs over the various arrays. This reply will concentrate purely on the overall disk size question. However, I do want to emphasize that it is VERY important to properly layout your files over multiple disks on the correct type of RAID and these things will affect the amount of disk space you need. [READ MORE]
|
| |
|
| | How to Layout Disk LUNS for SQL Server
Author :: louis nguyen
Date :: Tue 07/15/2008 @ 11:47 |
| PermalinkThis article summarizes the best practices for allocating disk LUNS for SQL Server for a standard SQL instance.
read article
HOW TO LAYOUT DISK LUNS
By Louis Nguyen
EXISTING VANILLA DATABASES
This article assumes we're not developing a new database or performance tuning an existing database. Instead we're trying to design storage for existing databases, for applications we have minimal knowledge of. The databases are vanilla databases with one MDF and one LDF file. This article addresses the standard case where a Microsoft SQL Server instance hosts multiple vanilla databases.
LDF
Of prime consideration are the LDF transaction log files. To explain, we have to delve into how MSSQL Microsoft SQL Server works. In order for a database transaction to be committed -- the disk subsystem must report back to MSSQL that the transaction was written to "stable media" or the LDF [1][2]. (Most disk subsystems have battery backed memory caching, which is another story). These writes to the LDF are sequential. Please note if the database recovery model is set to FULL, database operations such as rebuilding indexes write to the LDF also. For peak performance, each database should only have one LDF file. The LDF file would ideally be located on its LUN, separate from any other database LDFs or files [3]. And the LUN is RAID10. In practice, allocating a separate LUN for each LDF isn't realistic. However, for mission critical databases, there are very few reasons not to follow this best practice.
WHAT ABOUT SIMPLE RECOVERY
Databases in simple recovery mode actively use the LDF [4]. Transactions are still written to the LDF. LDF files can grow exponentially, if there is a runaway transaction. What is different is that after the transaction is committed, MSSQL will periodically truncate (but not shrink) the contents of the LDF.
WHAT ABOUT MDF
The MDF main database file, un-intuitively, is of less concern than the LDF. Again we have to delve into how Microsoft SQL Server works. Data pages are stored in a memory cache. This memory cache is known by several names: buffer cache or buffer pool or bpool [1][5]. When a page is modified, it is not immediately flushed to the MDF [6]. Instead, background processes such as checkpoint, eager write, and lazy write will later flush the dirty pages to disk. Several data modifications may be made to the buffer cache, before flushing occurs. When SQL Server has a read request, it reads from the buffer cache. If the page is not in the buffer cache, SQL Server copies the page from disk (MDF file) into the cache [7]. Assuming the server has adequate RAM, it is recommended to locate all MDFs in the same LUN. This LUN should be made up of many disk spindles [3].
TEMPDB
If the database heavily utilizes temp tables or features such as row versioning, TempDB MDF should be placed on its own RAID10 LUN. The TempDB LDF should be placed on a LUN dedicated to log files, preferably its own dedicated LUN [3]. Please note that on-line Index Rebuild utilizes row-versioning and TempDB [8]. For TempDB, it is also recommended to have N number of data files, where N is the number of CPUs [9].
BACKUPS
For database backups to disk, a separate LUN should be allocated. This is especially true if a database performs frequent transaction log backups. Writing backups to disk are sequential and may interfere with random OLTP activity.
PERFORMANCE COUNTERS
To measure how the disk subsystem is performing, one commonly used windows performance counter is Disk Queue Length. However, Average Disk Seconds Per Read/Write Transfer is a more reliable indicator [10]. Average Disk Seconds Per Read greater than 15 milliseconds indicates a bottle neck. Average Disk Seconds per Write can be as fast as 1 millisecond [11].
REFERENCES
[1] SQL Server 2000 I/O Basics
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
[2] SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability
http://support.microsoft.com/kb/230785
[3] SQL Server 2005 Configuration Blog #2.doc
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
[4] Checkpoints and the Active Portion of the Log
http://msdn.microsoft.com/en-us/library/ms189573.aspx
[5] Buffer Management
http://msdn.microsoft.com/en-us/library/aa337525.aspx
[6] Writing Pages
http://msdn.microsoft.com/en-us/library/aa337560.aspx
[7] Reading Pages
http://msdn.microsoft.com/en-us/library/ms191475(SQL.100).aspx
[8] Row Versioning Resource Usage
http://msdn.microsoft.com/en-us/library/ms175492.aspx
[9] Storage Top 10 Best Practices
http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx
[10] SQL Server Urban Legends Discussed
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
[11] Top SQL Server 2005 Performance Issues for OLTP Applications
http://sqlcat.com/top10lists/archive/2007/11/21/top-sql-server-2005-performance-issues-for-oltp-applications.aspx
|
| |
|
| | How to Configure Memory for SQL Server
Author :: Louis Nguyen
Date :: Tue 07/15/2008 @ 11:47 |
| PermalinkHow to configure operating system and SQL Server settings for optimum memory management.
read article
HOW TO CONFIGURE MEMORY FOR SQL SERVER
By Louis Nguyen
MAXIMIZE DATA THROUGHPUT FOR NETWORK APPLICATIONS
The "File and Printer Sharing for Microsoft Networks" property should be set to "Maximize Data Throughput for Network Applications" [1]. If "Maximize data throughput for file sharing" is selected instead, the operating system may limit the amount of memory available to SQL server for normal operation [2]. Please note BOL appears to give contradictory information about this setting. However [17] explains that LargeSystemCache should be set to zero for SQL Server. To set to zero, "... for Network Applications" should be set.
VIRTUAL MEMORY
The virtual memory page file should be set to 1.5 times the size of the server’s physical memory [3][4]. This is the default setting. SQL Server performs its own memory management. It consumes and releases memory, in response to the operating system [5]. Ideally SQL Server would only use virtual memory in the event of a memory dump.
LOCKED PAGES IN MEMORY
The account the SQL Server engine runs as, should be given the "Locked Pages in Memory" permission. "LocalSystem" has this permission. "Locked pages in memory" instructs the operating system not to page out the buffer pool into virtual memory [6]. For SQL 2005 64 Bit, only the Enterprise edition honors "locked pages in memory". With standard edition, the buffer pool may be paged out, resulting in performance degradation. The buffer pool is where SQL Server data pages reside. Note that "locked pages in memory" is a pre-requisite for enabling AWE.
MAX SERVER MEMORY
SQL Server "max server memory" should be set. "Max server memory" prevents the buffer pool from growing beyond the specified limit [7]. The buffer pool should be limited such that the other SQL Server components, other SQL Server instances, operating system, and any installed applications have adequate physical memory.
AWE
For 32 Bit servers, Address Windowing Extensions allows SQL Server to utilize physical memory beyond the 32 bit virtual address space limit. Refer to Microsoft Knowledge Base Articles 274750, 811891, and 899761 for enabling AWE [8][9][10][11].
WORKING SET TRIM
Processes do not access physical memory directly. Instead processes access virtual memory addresses (the virtual address space). Each process has a page map which translates the virtual addresses to physical addresses [12]. The subset of the virtual address space of a process that resides in physical memory is known as the working set [13]. Working set trim is when the normally physical memory resident portion of SQL Server processes have been paged out to virtual memory. SQL Server is designed to respond to "signaled trim". However, "self trim" and "hard trim" may result in performance degradation. According to Microsoft's Bob Dorr PSSBlog, working set trim may be caused by operating system bugs, drivers, or other application components [14]. Windows Server 2008 is said to alleviate this issue [15].
PERFORMANCE COUNTERS
To measure how well SQL Server is managing memory, three commonly used performance counters are "buffer cache hit ratio" and "page life expectancy" and "memory grants pending" [16]. Buffer cache hit ratio should be above 90 percent. This means that 90 percent of the requested data pages were found in the buffer pool and SQL Server did not have to read from the database files. Page life expectancy should be above 300 seconds or 5 minutes. This means that data pages in the buffer pool have an average life of 300 seconds before they are discarded. Memory grants pending should average to zero. This means that no requests are waiting on memory.
REFERENCES
[1] Maximizing Data Throughput
http://msdn.microsoft.com/en-us/library/ms191471.aspx
[2] Enabling Memory Support for Over 4 GB of Physical Memory
http://msdn.microsoft.com/en-us/library/ms179301.aspx
[3] Configuring Virtual Memory
http://msdn.microsoft.com/en-us/library/ms187877.aspx
[4] How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
http://support.microsoft.com/kb/889654/
[5] Dynamic Memory Management
http://msdn.microsoft.com/en-us/library/ms178145.aspx
[6] How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
http://support.microsoft.com/kb/918483
[7] Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067.aspx
Please note this article contradicts [2] regarding Maximizing Data Throughput for Network Applications and should be corrected. [8] How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750
[9] SQL Server only uses 2 GB of memory even though the AWE option is enabled
http://support.microsoft.com/kb/811891
[10] FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/kb/899761
[11] Do I have to assign the Lock Pages in Memory privilege for Local System?
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx
[12] Virtual Address Space
http://msdn.microsoft.com/en-us/library/aa366912.aspx
[13] Virtual Address Space and Physical Storage
http://msdn.microsoft.com/en-us/library/aa366914(VS.85).aspx
[14] The SQL Server Working Set Message
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
[15] SQL Server Working Set Trim Problems? - Consider...
http://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.aspx
[16] Top SQL Server 2005 Performance Issues for OLTP Applications
http://sqlcat.com/top10lists/archive/2007/11/21/top-sql-server-2005-performance-issues-for-oltp-applications.aspx
[17] LargeSystemCache
technet2.microsoft.com/windowsserver/en/library/efa621bd-a031-4461-9e72-59197a7507b61033.mspx
|
| |
|
| | Preventing SQL Injection Attack
Author :: Cindy Gross
Date :: Tue 07/15/2008 @ 03:35 |
| PermalinkSQL Injection is a method where people are able to change the code submitted to SQL to something else. It’s not something you can prevent on the database side with a configuration change or anything like that, though improving your server side security is key. You have to change your stored procedures, functions, and dynamic TSQL code to not allow the potential for injection of other code. The same basic steps apply whether it is SQL Server, DB2, Oracle, or any other relational database. [READ MORE]
read article
SQL Injection is a method where people are able to change the code submitted to SQL to something else. It’s not something you can prevent on the database side with a configuration change or anything like that, though improving your server side security is key. You have to change your stored procedures, functions, and dynamic TSQL code to not allow the potential for injection of other code. The same basic steps apply whether it is SQL Server, DB2, Oracle, or any other relational database. [READ MORE]
|
| |
|
| | Paging in Sql Server
Author :: Kaushal Parik
Date :: Thu 07/10/2008 @ 06:57 |
| PermalinkDescribes about implementing Paging functinality in Sql Server while having thousands of data in your database.
read article
(this is one of the article i posted on our local intranet repository; hope it will be helpful to others) Web Application Performance (Paging in Sql Server) As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.
|
|
|
|
|
| |