March 2008 - Posts
Guideline for Resolving Deadlock
By : Kasim Wirama, MCDBA
In shared database resource where concurrent access always occurs, you might see deadlock. If you want to get more information about deadlock in SQL Server 2005 such as latest SQL statement before deadlock and types of deadlock, you need to turn on 1222 trace flag in SQL Server 2005.
After you get those information and analyze it, then follow these guidelines to resolve deadlock.
1. Be aware which solution you will choose, short term or long term.
For example in short term solution, you add hints such as NOLOCK at SELECT statement, and for long term, you need to change to code that participates in transaction and test it.
2. Shorten the transaction if possible
3. Identify missing index and add it.
4. Retry transaction if deadlock happens.
To identify if the deadlock happens, use error handling function ERROR_NUMBER(), error number for deadlock is 1205
5. Lower deadlock priority of one process with SET DEADLOCK_PRIORITY <number>, with number ranging from -10 to 10 or one of this options (LOW, NORMAL, or HIGH) in SQL Server 2005.
Other resource you need to refer is SQL Server Books Online, and search for topic “Minimizing Deadlocks”.
Scalability Improvement with Temporary Table Caching in SQL Server 2005
By : Kasim Wirama, MCDBA
SQL Server 2005 has made interesting improvement on tempdb database. Probably, it will apply as well to SQL Server 2008. Pressure on tempdb database that is caused by read/write contention will be greatly reduced. You can observe with dynamic management view (DMV) sys.dm_os_performance_counter filtered by name column (Temp Tables Creation Rate), column cntr_value.
Let’s see this below example.
CREATE PROCEDURE dbo.test_caching
AS
CREATE TABLE #temp1 (cola int, colb int);
DECLARE @i AS INT;
SET @i = 1;
INSERT INTO #temp1 VALUES (@i , @i + 100);
DROP TABLE #temp1;
The stored procedure above will create temporary table, fill with some value, and drop it. Then I call the stored procedure from another batch as below
DECLARE @i AS INT;
SET @i = 0;
WHILE @i < 10
BEGIN
EXEC dbo.test_caching
SET @i = @i + 1;
END
Before the iteration gets executed, query the DMV to get cntr_value, you might get different value than I do. In my computer, I get the value : 15. Now execute the iteration, and query back the DMV, amazingly I still get only 1 incremented value (i.e. 16), even though I iterate it by more than once (10 times).
Now change the stored procedure to create unique clustered index on cola as below:
ALTER PROCEDURE dbo.test_caching
AS
CREATE TABLE #temp1 (cola int, colb int);
CREATE UNIQUE CLUSTERED INDEX IX_1 ON #temp1(cola);
DECLARE @i AS INT;
SET @i = 1;
INSERT INTO #temp1 VALUES (@i , @i + 100);
DROP TABLE #temp1;
Measure the DMV’s cntr_value, this time the value is 16 (in my computer). Execute the iteration code above. After execution, I get cntr_value is 26, from here I know that temporary table is not cached for each iteration, and it will impact negatively to performance.
To make tempdb cache works, you need to avoid some condition in temporary table creation :
1. Do not create index and constraint explicitly.
2. Do not create temporary index in dynamic SQL.
3. Tempdb cache only works when it is created in database routine.
That’s the trick!
Type of Deadlocks
By : Kasim Wirama, MCDBA
When concurrent accesses go to shared resource, there might be potentially deadlock issue. There are 2 conditions whether a deadlock might happen. First, deadlock needs at least two tasks or more and at least one exclusive lock exists to initiate deadlock. Remember that the longer your transaction, the more probability that deadlock happens. Principally deadlock problem is incompatibility lock of writer.
There 4 types of deadlocks. So this article will explain those types of them below.
1. Conversion deadlock
Here is the illustration. There are 2 connection, that each of them accesses same resource with shared lock, initially it has no problem because shared lock is compatible each other. Deadlock happens when each of the resource changes their lock from shared lock to exclusive lock.
2. Writer-writer deadlock
This kind of deadlock happens when two connection access two resource in different order when do writing activities. Connection A accesses resource A with exclusive lock, connection B accesses resource B with exclusive lock, then connection A accesses resource B with exclusive lock and connection B accesses resource A with exclusive lock as well. The final condition will result none of the connections will release their resource except SQL Server must to choose one of them as a deadlock victim.
3. Reader-writer deadlock
That’s the most common deadlock. The situation happens when two connection hold exclusive locks on different resource and then each of the connection requests shared lock on their partner connection. For example:
Connection A holds exclusive locks on resource A.
Connection B holds exclusive lock on resource B.
Connection A requests shared lock on resource B.
Connection B requests shared lock on resource A.
4. Cascading deadlock
This deadlock happens when more than two connections involved in deadlock. Even though SQL Server has choose one of them as deadlock victim, but another deadlock still exists by other connections, so SQL Server repeats the action to kill the other connection until no deadlock found.
Tempdb Space Planning and Space Monitoring
By : Kasim Wirama, MCDBA
Most of time, users don’t have to worry about running out space on tempdb as long as you have enough disk space to dealing with tempdb growth. Sometimes your tempdb has grown to considerably large so that it consumes all available disk space where the tempdb file resides.
Behavior of tempdb growth is influenced by these factors : snapshot isolation, triggers, MARS, hash join, hash aggregate, online index rebuild. Hash join and hash aggregate consumes space in tempdb, usually optimizer choose them because involved tables doesn’t have correct index or even query hints. If this is the case, you can save unnecessary tempdb space by implement correct index that supports your SQL query.
Planning for tempdb space is not simple tasks. Many factors that I have mentioned above needs to be consider, and usually the estimation is far incorrect than the real one. What you need to do is to do rough estimation and prove it with testing data. This activity will be done iteratively until you get the close number for tempdb space planning. Here is the points as your guidance in determining tempdb space requirement for initial :
a. What are factors that will be included in tempdb.
b. How much space for each factors if queries is done serially. You need to consider maximum space requirement and most common requirement that may apply.
c. How much space whose features will be executed concurrently.
Some users through mailing list ask about how to shrink log file and whether it is necessary to shrink it. I suggest to shrink the log file up to size when the tempdb is under the busiest workload. Let’s say in a month the tempdb size requires 20 GB, but at the end of a month, the tempdb size requires 40 GB, it makes sense if the tempdb is shrink up to 40 GB.
Next activity is about monitoring tempdb space. You can collect tempdb information through execution these queries below based on interval basis and it doesn’t cause performance issue.
SELECT * from sys.dm_db_session_space_usage; SELECT * FROM sys.dm_db_file_space_usage;
You get the important information here. Observe information about version store. If the version store is the source of aggressive tempdb growth issue, consider to make less use of the versioning feature or add additional disk space. But if this version store is not the source of problem, most likely user objects information is the issue. If this is the case, consider to add some additional disk space or you modify SQL statement to make tempdb less pressure regarding to disk space.
TempDB Improvement in SQL Server 2005
By : Kasim Wirama, MCDBA
In performance tuning area in SQL Server, there are five resources that are potential to contribute performance bottleneck. They are CPU, memory, I/O, tempdb and blocking. I will talk about CPU, memory and blocking in separate article. I would like to look into tempdb database.
SQL Server 2005 makes interesting improvements in tempdb database and let’s see improvements made in SQL Server 2005.
1. Less I/O activity in tempdb log file.
See this script below :
CREATE TABLE #temp1 (ColA CHAR(1000));
INSERT INTO #temp1 VALUES (‘test’);
UPDATE #temp1 SET ColA = ‘test updated’;
Instead of before and after changes are logged into tempdb log file (total 2000 byte), only 1000 byte is written into tempdb log file, so it explains less I/O activity in log file. But it doesn’t apply if the temporary table has clustered index or contains LOB. And the benefit will come for INSERT and UPDATE statement only.
2. Faster growing data file
In SQL Server 2000, file growth takes awhile. And if this activity frequently happens, it will degrade performance. SQL Server 2005 improves it so that file growth happens instantaneously. What is the trick behind this? The trick is that SQL Server 2005 leverages windows API to expand file size without performing I/O activity on new area of the file expansion. This only works if SQL Server 2005 is installed in Windows XP, Windows 2003 or Windows 2008.
3. Scalable concurrent access.
Consider this script below :
CREATE TABLE #temp1 (ColA CHAR(1000));
INSERT INTO #temp1 VALUES (‘test’);
DROP TABLE #temp1;
If large number of user execute this script above, it will create intensive CREATE/DELETE activity. Such a intensive activity will cause read/write contention in page level because each calling for the batch above will make tempdb database doing these activities:
a. Insert new metadata entry into system table.
b. Allocate 2 pages, one for data page, and the other for IAM page. PFS, GAM, and SGAM pages will be scanned and updated to find and allocate the pages.
c. When DROP TABLE is issued, tempdb will free up the 2 pages.
d. Remove related metadata entry in system table.
Better scalability in SQL Server 2005 when dealing with these patterns is that :
a. Change internal page allocation algorithm in allocating new pages so that read/write contention is reduced.
b. Cache metadata creation and deletion in memory cache.
It will make those script will be scalable when executed in terms of concurrent access.
4. Better utilization of multiple tempdb files.
If you have multiple CPU, it is recommended you have proportional number of tempdb files to corresponding to number of CPU. It is done so to avoid page latch because of read/write PFS/GAM/SGAM pages contention. Implement file-level striping to achieve better I/O bandwidth. SQL Server 2005 have better algorithm so that each files will be proportionally filled up. At the end, all files will reach their maximum capacity almost the same time.
Consideration to Choose between T-SQL and CLR-SQL
By : Kasim Wirama, MCDBA
.NET integrates to SQL Server commenced from SQL Server 2005, and it will tighter in subsequent versions of SQL Server release (2008 and so on). I see that this architecture is nice but prone to cause database performance problem if you don’t consider some aspects that influences the choice of using between two of them. So I can say that it is double-edge sword.
With use of CLR-SQL into database objects such as stored procedure, user defined functions, user defined aggregates and user defined types, you have more options to leverage .NET functions into SQL Server by leveraging .NET framework classes. If you face with procedural and intensive computation, CLR-SQL is better choice rather than T-SQL. For example, instead of using cursor, it has better performance when you replace it with DataReader from .NET. More options are available in CLR-SQL for string manipulation and .NET API encryption. CLR-SQL is safer and does similar functionality in extended stored procedure that is previously written in C++. And Visual Studio will make development and deployment CLR-SQL even easier. You cannot make more programmatic user data type and custom user defined aggregate with T-SQL, but it is not the case in CLR-SQL. Clearly, it is area that CLR-SQL can have room to improvise.
So can all functionality of T-SQL be replaced with CLR-SQL? No. T-SQL is powerful when set-based access is required, so Microsoft will make it even interesting with inclusion of high performance analytical functions such as ROW_NUMBER, RANK(), DENSE_RANK(), NTILE() and PIVOT(), UNPIVOT(), TRY CATCH, etc. In trigger area, CLR-based trigger is not viable options, and T-SQL will perform better.
Pro and Cons between Ad Hoc SQL and Stored Procedure
By : Kasim Wirama, MCDBA
Before I give the pro and cons between Ad Hoc SQL and stored procedure, I would like to define what Ad Hoc SQL is. Ad Hoc SQL is SQL statements that is emitted from application into database. They are SELECT, INSERT, UPDATE and DELETE. Ad hoc SQL is generated runtime by application into database. In online and offline discussion, some professionals prefer using ad hoc SQL rather than stored procedure, and the others prefer using stored procedure.
For me, both have advantages and disadvantages, it depends the scenario that directs your architecture. Here are some points I find here.
When you need flexibility with every possible query that comes from application tier into database tier, ad hoc SQL is preferred way to go. Performance might be better in case of you just need necessary columns in SQL statements, not all of them.
When you use ad hoc SQL, you will make your application low cohesion and high coupling. Even small changes will impact to changes to your application tier as well. If you emit some SQL statements as one batch, the complexity will rise, and it will not be easy to maintain. Of course, care should be taken to safeguard the ad hoc SQL from possibility of SQL injection. In case of performance issue in your application, it is harder to do because many possibilities come from ad hoc SQL and you need to change and re-deploy your application.
The good point, when stored procedure is used, is that they provide encapsulation from complex logic. Dynamic SQL can be safely implemented in stored procedure and the problem could be easier to handle because it is localized. You can grant stored procedure only instead of giving user to directly interact with tables in a database. Performance tuning would be easier to do in stored procedure rather in ad hoc SQL.
Do you think there are no cons for using stored procedure? Not really. Stored procedure is more rigid rather that ad hoc SQL so don’t put frequently-changed business logic and rule into stored procedure, better to put them in application tier. You have limited options to make just necessary columns involved dynamically.
Hopefully, it will give you guidance which one is appropriate to implement based on your nature of application.
Database Interoperability
By : Kasim Wirama, MCDBA
You need to consider your SQL syntax among database platform accessed from your application such as .NET, Java, etc. Interoperability in database is some of database issue that you need to consider and it seems that it is not easy to make your SQL code is portable among database platform.
I refer to these database platforms because they are widely used in database market. They are SQL Server, Oracle, DB2, and MySQL. I can suggest that the more you stick to ANSI standard, the more portable your SQL code, at least you doesn’t need much effort by doing little changes.
Here are some aspects as guidance to make your database more portable:
1. Avoid these data types : IMAGE, SQL_VARIANT, TABLE, VARCHAR(max) and TEXT when you stick with ANSI standard.
2. Avoid quoted, bracketed identifier.
3. Use ANSI only for INSERT, UPDATE, and DELETE.
4. Avoid using hints (beginning with WITH keyword) because they are specific to each database platforms.
5. Avoid using INSERT…. EXECUTE, because only SQL Server has this handy feature. I love this construct but for interoperability this should be sacrificed.
6. Avoid these SELECT constructs in SQL Server.
SELECT….INTO…FROM
WITH CUBE/ROLLUP
COMPUTE
FOR XML
7. If you interoperate your SQL Code between SQL and Oracle you can partition your table/index, otherwise avoid partitioning.
Other aspects that you can’t avoid to make effort on SQL interoperability is that database functions and stored procedure because ANSI just approaches procedural extensions in ANSI-2003, not specific extensions.
Implement Concurrency-Safe Stored Procedure for Update and Delete
In SQL Server 2005
By : Kasim Wirama, MCDBA
Concurrency happens when two or more users access and modifies same resource. In this article, I would share would how you should develop UD (Update, Delete) stored procedure that is aware to concurrency so that your users ensure that changes that they make will be consistent.
Create 2 tables, first invoice and then invoiceLineItem as script shown below :
CREATE TABLE dbo.Invoice
(
InvoiceId int identity(1,1),
InvoiceDate datetime,
InvoiceNumber varchar(30) not null,
Constraint pk_invoice primary key (InvoiceId),
Constraint ak_invoice unique (InvoiceNumber)
);
GO
CREATE TABLE dbo.InvoiceLineItem
(
InvoiceLineItemId int identity(1,1) not null,
InvoiceId int not null,
Cost numeric (10,2),
Constraint pk_invoiceLineItem primary key (InvoiceLineItemId),
Constraint fk_invoiceLineItem foreign key (InvoiceId) References dbo.Invoice(InvoiceId)
);
GO
Now let’s say you create a stored procedure to delete one of the invoiceLineItem as follows :
CREATE PROCEDURE dbo.InvoiceLineItem_del
(@InvoiceLineItemId int)
As
BEGIN
DELETE FROM dbo.InvoiceLineItem
WHERE InvoiceLineItemId = @InvoiceLineItemId
END;
GO
It’s simple but I can say it’s not safe against concurrency issue. Let’s say, user A change cost at a InvoiceLineItem. With almost the same time, user B call the stored procedure to delete the InvoiceLineItem. Boom! User B successfully delete the invoiceLineItem without knowing before hand that there was a change in InvoiceLineItem made by other user, i.e. UserA. That’s the concurrency issue.
So the stored procedure above is not capable to handle concurrency issue, and unfortunately I saw many projects get used to with this kind of CRUD style which is not concurrency safe.
How to make it concurrency safe? Here I will tell you a trick. The tirck is that I add rowversion at Invoice and implement high performance server side transaction scope that binds Invoice and InvoiceLineItem into one transaction. So I add new column at Invoice table as following script shown:
ALTER TABLE dbo.Invoice
ADD ObjectVersion rowversion;
Now change the above stored procedure as follow :
ALTER PROCEDURE dbo.InvoiceLineItem_del
(
@InvoiceLineItemId int ,
@InvoiceId int ,
@objectVersion rowversion
)
As
BEGIN
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.Invoice
SET InvoiceNumber = InvoiceNumber
WHERE InvoiceId = @InvoiceId
AND ObjectVersion = @objectVersion;
IF @@ROWCOUNT = 0
RAISERROR (‘Changes has been happened prior deleting line item’, 16, 1) ;
DELETE dbo.InvoiceLineItem
WHERE InvoiceLineItemId = @InvoiceLineItemId;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF xact_state() <> 0
BEGIN
ROLLBACK TRAN;
END
DECLARE @errmsg VARCHAR(8000);
SET @errmsg = ERROR_MESSAGE();
RAISERROR ( @errmsg, 16, 1);
END CATCH;
END;
GO
With the concurrency-safe stored procedure above, your users will be aware changes made from others before hand.
MCDBA (Microsoft Certified Database Administrator) is Only Paper Certified?
By : Kasim Wirama, MCDBA
In offline discussion, some mailing list user asks what the proper salary is appropriate for a MCDBA. I think it depends on level that the use expects from a MCDBA, entry level, middle level or advanced level.
Anyway, a MCDBA should know all features of SQL Server, including how to design database with flexibility and extensibility while maintaining performance within baseline in future time. But other thinks that MCDBA is worthless, it should be same level as software developer. I am not sure whether I disagree or agree with it because at some points they are right but not absolutely right. But a clear distinction comes to my insight in what extent a MCDBA could give value to software engineering in database development and administration.
A MCDBA should have proven experience in database area, how he could maintain data integrity and data quality, transform data into information to end user, provide high availability solution against potential data loss because of disaster.
What is the crucial asset in a company that relies on database? The answer is DATA. How a MCDBA secures and maintain the data is key role to ensure success of business continuity of a company. Data loss is a big disaster for a company, the impact is that customer trust will be breached and company will loss most of the potential benefit from it.
That is critical mission that a MCDBA should have such a responsibility. If a holder of MCDBA is a professional that doesn’t have strong database background experiences, I can say that the individual’s certification doesn’t emphasize value and potential on him. Vice versa, a MCDBA certification is a form of Microsoft recognition that the holder has mastered the SQL Server product and he is considered to be ready to dealing with database stuff.
Considering the responsibility of MCDBA is more critical rather than developer, it could have more important position among any roles in software development. So, it is questionable that salary for MCDBA is equal to developer’s. Some people think that appreciation is too high for MCDBA, but I think that’s the international standard that a MCDBA should get this reward because of heavy responsibility to keep data integrity and availability all the time. Does it make sense? Agree and disagree is both side of a coin.
Status Pembuatan Buku Pemrograman Transact SQL
(SQL 2000 dan SQL 2005)
Sekitar kuartal keempat tahun lalu (2007), saya sempat mengundang rekan-rekan untuk berkontribusi di dalam pembuatan buku pemrograman Transact SQL. Sedianya saya targetkan selesai bulan November, tapi ternyata meleset karena masing-masing kontributor sibuk dengan kesibukan kantor dan lainnya, untuk itu mohon maklum. :)
Saya optimis sekali buku ini akan launching karena progress sudah selesai semua kecuali hanya 1 bab saja. Bab yang belum selesai ini justru menurutku sangat menarik dan sangat penting, yang bila dikuasai akan mendongkrak nilai jual seorang DBA SQL Server.
Moga-moga aktivitas menulis ini menular kepada rekan-rekan milis terutama milis SQL Server Indo. Anda tentu ingin menjadi salah satu kandidat berikutnya untuk MVP SQL Server bukan? dengan berkontribusi secara offline maupun online. btw MVP SQL Server di Indo bisa dihitung dengan sebelah tangan, sehingga peluang masih terbuka luas untuk berkontribusi di dalam arena SQL Server.
Kembali ke topic, tunggu saja tanggal main penerbitan bukunya. J
Berikut kerangka pembahasan setiap bab :
Project Ebook Transact SQL Topik : Tips dan Trik pemrograman Transact SQL
Bab I Urutan pemrosesan query
Bab ini memberikan penjelasan kepada pembaca urutan pemrosesan item-item sebuah query (SELECT, column list, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY) sehingga pembaca bisa mengerti dan mandapatkan hasil query sesuai yang diharapkan. Bab II Tuning query
Bab ini menjelaskan problem di seputar query dan praktek tuning. Bab III Teknik query tingkat lanjut
Bab ini menjelaskan syntax query di TSQL 2005 yang diterapkan pada sejumlah kasus bisnis.
Bab IV Penerapan CTE (Common Table Expression)
Bab ini menjelaskan jenis-jenis hirarki dan bagaimana solusi-solusi TSQL diterapkan atas jenis-jenis hirarki tersebut.
Bab V Penanganan eksepsi di dalam TSQL
Bab ini menjelaskan kepada pembaca mengenai penanganan eksepsi di pendahulu SQL Server 2005 dan SQL Server 2005, dan penerapan yang handal di dalam TSQL terutama TSQL 2005
Bab VI Versi baris dan isolasi level baru di SQL Server 2005
Bab ini menjelaskan kepada pembaca mengenai latar belakang dari teknologi versi baris dan penerapan versi baris dan jenis-jenis isolasi level yang baru di SQL Server beserta penjelasan mengenai kelebihan dan kekurangannya.
Tips : siapkan laptop, secangkir kopi panas dan tidur yang cukup sebelum menempa diri anda dengan buku ini.
Dengan Piece Meal Online Restore di SQL Server 2008,
Saatnya Mission Critical Industry Migrasi ke SQL Server 2008
by : Kasim Wirama, MCDBA
Dalam rangka menaikkan tingkat penetrasi SQL Server ke mission critical application di dunia finance seperti core banking, perlu kiranya mengetahui fitur teranyar dari SQL Server 2008 terutama dari sisi backup dan restore.
Selama ini SQL Server diketahui memiliki kemudahan dalam penggunaan, dan sering digunakan sebagai development platform, namun perlu diketahui sisi administrative (termasuk performance tuning) database tidak kalah pentingnya terutama dalam fase production. Pemanfaatan sisi administrative SQL Server dirasa cukup basic sejauh GUI mendukung, namun sesungguhnya skrip sql yang paling powerful dan bila menguasai skrip dan konsep maka skenario yang kompleks sekalipun bisa diatasi.
Pada SQL Server 2000 dan sebelumnya, bila terjadi disaster pada salah satu file (seperti corrupt) berarti akan membawa downtime database secara keseluruhan. Namun dengan SQL Server 2008, downtime tersebut tidak diperlukan lagi, file yang tidak corrupt masih tetap bisa beroperasi dan database bisa di offline-kan hanya untuk file yang corrupt saja, dan backup-restore log hanya ditujukan kepada file yang corrupt itu saja selagi database tetap online. Berdasarkan salah satu fitur enterprisenya, saya bisa katakan SQL Server 2008 jauh lebih skalabel.
Masih banyak lagi fitur-fitur administratif skala enterprise di SQL Server 2008. Bila seorang DBA menguasai fitur-fitur administratif (tanpa harus mengandalkan GUI), bisa dikatakan dia adalah DBA for all weather. Saya akan share fitur-fitur administrative skala enterprise pada tulisan-tulisan berikutnya. Semoga di waktu-waktu mendatang, makin banyak DBA SQL Server mendominasi industri mission critical seperti core banking sehingga mendongkrak kebutuhan akan DBA SQL Server.
Dengan license cost jauh lebih murah dan fitur enterprise yang dibundle menjadi satu bila dibandingkan dengan database vendor kompetitor yang mematok license terpisah untuk fitur-fitur enterprisenya, adalah merupakan keputusan yang tepat bila memilih SQL Server 2008