May 2008 - Posts
Pembahasan mengenai metode mana yang memberikan performance lebih baik saat membuat tabel ini sempat dibahas sedikit di milis SQL Server User Group Indonesia . Hal ini menurut saya memang menarik untuk dibahas lebih lanjut dan didiskusikan. Sebelum saya membahas lebih lanjut tentang table-scan method dan clustered index scan method, saya akan membahas sedikit mengenai organisasi data pada SQL Server 2005.
Organisasi data pada SQL Server 2005
Setiap tabel pada database memiilki sebuah tabel sysindex yang memiliki index id (indid) yang berbeda-beda. Indid ini nilainya bervariasi mulai dari 0 hingga 254. Heap table memiliki indid 0, sedangkan clustered index table memiliki indid bernilai 1, dan non-clustered index table memiliki indid bernilai 2-254. Saat dilakukan pencarian data, maka SQL Server akan melihat FirstIAM (Index Allocation Map) tabel tersebut dan mengenali apakah tabel tersebut memiliki index (baik clustered maupun non-clustered) lewat indid pada tabel sysindex tersebut.
Cara kerja table-scan dan clustered index scan
Berikutnya adalah bagaimana cara kerja table scan dan clustered index scan. Ilustrasi mengenai cara kerja table scan dan clustered index scan, pernah saya bahas pada artikel sebelumnya. Table scan method dapat diumpamakan seperti mencari sebuah kata pada tabel yang tidak memiliki index. Anda mungkin dapat menghabiskan waktu untuk mencari kata tersebut pada buku yang saya maksud, sedangkan clustered index scan dapat diumpamakan seperti index huruf pada kamus, Anda hanya perlu mencari huruf yang sesuai dengan huruf pada awal kata yang Anda cari. Hal ini tentu menyebabkan waktu pencarian dengan kedua metode tersebut berbeda. Clustered index scan akan mengoptimalkan waktu pencarian Anda. Demikian pula yang terjadi pada SQL Server. Clustered index table menggunakan struktur B-Tree (root-intermediate-leaf) yang akan membantu optimalisasi waktu pencarian data. Sebenarnya heap table juga menggunakan konsep ini, namun ada perbedaan yang terdapat antara kedua metode ini. Hal tersebut adalah :
- Heap table minimal membutuhkan 4 jump untuk mencari data. Jump tersebut antara lain : pencarian di root page, intermediate page, leaf node, dan data page itu sendiri. Hal ini dikarenakan data pada leaf node adalah pointer data, sehingga SQL Server perlu mencari lagi data yang Anda maksud pada harddisk. Oleh karena itu, IAM pada table scan lebih tepat disebut sebagai Storage Allocation Map (SAM). Hal yang pertama kali dilihat oleh SQL Server saat melakukan table scan adalah FirstIAM (Index Allocation Map) tabel tersebut. Saat indid bernilai 0, yang berarti tabel tersebut merupakan heap table, maka SQL Server akan mencari pada header page database. Hal inilah yang menyebabkan waktu eksekusi pada heap table menjadi lebih lama.
- Clustered index table hanya membutuhkan 3 kali jump untuk mencari data. Jump tersebut antara lain : pencarian di root page, intermediate page, dan leaf node. Saat mencapai leaf node, data yang Anda cari sudah terdapat disana, sehingga waktu pencarian pun akan menjadi lebih singkat. Selain itu, data yang dimasukkan pada tabel yang memiliki clustered index akan 'dipaksa' untuk disimpan secara terurut berdasarkan index. Hal ini akan membuat pencarian data lebih cepat.
Ada beberapa hal yang perlu Anda ingat disini, walaupun pada clustered index table data sudah 'terurut' dengan baik, Anda perlu mengaktifkan update statistic atau secara rutin mengupdate statistic index. Hal ini bertujuan untuk mencegah terjadinya fragmentasi index.
Nah, setelah penjelasan mengenai hal ini, semoga pengertian konsep pencarian data dengan kedua metode ini akan lebih baik dari sebelumnya.Sebenarnya saya agak takut membahas hal ini, dikarenakan takutnya mispersepsi yang mungkin akan timbul setelahnya. Untuk itu saya akan menyertakan referensi yang saya gunakan untuk penulisan artikel ini, agar rekan-rekan bisa mereview ulang artikel saya ini. Semoga berguna buat rekan-rekan.
Referensi yang digunakan :
SQL Server Books Online
MSDN Article : SQL Server Optimization
SQL Druid Article mengenai Optimization in SQL Server
Sybex - Implementation and Maintenance MS SQL Server 2005
Regards,
Rangga Praduwiratna, MCP, MCTS : SQL Server 2005
There are several types of backup methods in SQL Server 2005. In this time, I'll tell you about full backup, differential backup, incremental backup, and transaction log backup.
Full Backup. This type of backup will lets you to backup all of extents of your database. In my previous articles, I've already explain about extent in SQL Server 2005. SQL Server always save your data in pages and extents. 1 pages contains 8 Kb of your data, and 1 extents is a group of 8 pages which contains 64 Kb data of your database. To use this type of backup, you must set your recovery model to full, before you backup your database. To set your database recovery model, you can use this query :
ALTER DATABASE [ database_name ]
SET RECOVERY [ FULL | BULK_LOGGED | SIMPLE ]
,and you can do full backup by using this query :
BACKUP DATABASE [ database_name ] TO DISK = '<directory>\<filename>' WITH INIT
TO DISK clause specify the location of your backup device, while WITH INIT clause is the common clause to tell SQL Server to overwrite existing data in backup device. You can make backup device by using SSMS or simply by using this query. Backup device is logical backup medium to save your backup data. It has extension .bak.
Differential Backup. This type of backup will backup your database since the last full backup. SQL Server will make extent map to recognize the new data in your database. When you insert data in your database, extent will have bit with 0 to 1 to represented their information. After you do full backup, it will be reseted to 0, in this way SQL Server know which data that have to be backed up in differential backup methods.Notes that you can do this type backup if you have done full backup to your database.
You can do differential backup by using this query :
BACKUP DATABASE [ database_name ] TO DISK = '<directory>\<filename>' WITH DIFFERENTIAL
Incremental Backup. This type of backup looks similar to differential backup. But actually, it's really different. Incremental backup will back up your database since the last full backup and the last incremental backup. So if you have 100 Mb data on Sunday, 150 Mb data on Tuesday, 200 Mb data on Wednesday, and 250 Mb data on Thursday. So the size of full backup data taken on Sunday is 100 Mb, while the incremental backup data taken on Tuesday, Wednesday, and Thursday are 50 Mb data for each day.
To restore this incremental backup, you must have all of your incremental backup data, or you won't be able to restore your database completely. For example, your incremental backup data on Wednesday is lost, you won't be able to restore your incremental backup data on Thursday. This type of backup are not recommended for production database, since it has a lot of risks.
Transaction Log Backup. This type of backup needs full backup of your database. Transaction Log Backup will back up all actives log files of your database. To use this type of backup you can use this query :
BACKUP LOG [ database_name ] TO DISK = '<directory>\<filename>' WITH INIT
You also can make operators and scheduled tasks to be assigned backup job to automated backup process of your database. I won't explain it this time, but I promise I'll tell you about this later.
Regards,
Rangga Praduwiratna
Partitioning is a new functionality in SQL Server 2005. It lets you split your tables to several filegroups, so user can access your table faster. Partition acts like an object, so you can assign it to your destination table.
You can create partition function by using this query :
CREATE PARTITION FUNCTION partition_fn_name (input_parameter_type)
AS RANGE [LEFT | RIGHT]
FOR VALUES ([ boundary_value ]) [;]
The boundary_value lets you to control how many rows in your table to be separated. Below is illustration for this query :
CREATE PARTITION FUNCTION pFunc (int)as
RANGE LEFT FOR VALUES (100,200);
It means you create partition with LEFT RANGE, your partition will be : infinity to 100, 101-200, and 201 to infinity. If you specify RIGHT range, then your partition will be like this : infinity to 99, 100-199, and 200 to infinity. After you create partition function, you must create a partition scheme to defines the physical storage structures or filegroups that have been made before.
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
TO ( [ filegroups_name ] ) ( ; )
Notes that if you make 2 boundary_value, the result will be 3 partitions, so you must specify 3 filegroups. If you want to use only one filegroups, you can use ALL TO clause rather than TO clause. You can use this partition function and scheme by using query like this :
CREATE TABLE KTP
(KTPID int identity(1,1) PRIMARY KEY CLUSTERED,
NamePenduduk varchar(50) not null,
Address varchar(50) not null)
ON partition_scheme_name(KTPID);
In above example, you put partition scheme and function on KTP ID field in Table KTP, since it has index. You also can put partition function on different tables or fields. You also must make filegroups' used by partition scheme before execute this query. I said before that you can increase your database server performance by creating partitions, since you can put your filegroups to the best hardware that have the fastest READ/WRITE time. For example RAID 5.
Regards,
Rangga Praduwiratna
Until June 30, 2008 Microsoft open Microsoft Academic Second Shot Program. You can use this program to get certified by Microsoft with a 40% discount and also get second shot if you fail the test. In this program the prefix for the test is 72, but it has no differences with 70 prefix exam. 72 prefix only indicates that you get the certificate from academic program.
FYI : Not all of Microsoft exam is opened in this program. For a complete list of exam in this program, you can check in here.
Penggunaan index pada database merupakan salah satu teknik
pembuatan database yang baik. Hal ini terutama sangat berguna pada implementasi
database dengan skala VLDB (Very Large
Database) atau OLDB (Online Large
Database). Saat database dibuat tanpa menggunakan index, maka kinerja
server database dapat menurun secara drastis. Hal ini dikarenakan resource komputer banyak digunakan untuk
pencarian data atau pengaksesan query SQL
dengan metode table-scan. Index pada kolom-kolom
tabel database mempunyai fungsi seperti indeks kamus atau indeks buku. Hal ini
membuat pencarian data akan lebih cepat dan tidak banyak menghabiskan resource komputer. Oleh karena itu,
memiliki pemahaman akan konsep indexing, teknik implementasi, dan memahami
kapan saat tepat untuk menerapkan metode ini merupakan hal yang berguna
terutama bagi para enterprise application
developer atau database administrator.
Anda dapat mendownload file pdf-lengkap melalui link dibawah ini :
Download artikel penggunaan index pada SQL Server 2005
Tulisan ini juga dipublikasikan di situs ilmukomputer.com.
Semoga bermanfaat. Salam!
Rangga Praduwiratna
There's a lot of ways to increase performance of our databases in SQL Server 2005. And there's so many tutorials about these. Like implement index, use of stored procedure, etc. But actually, how we can monitor our database performance in SQL Server, so we could review our works.
In this tutorial, I'll try to give a simple tutorial about monitor SQL Performances.
Performance Monitor
Yap, it's that easy! Use performance monitor if you want to know about your database performance. Easy and simple. We maybe know about these features in Windows, but, how do we use it?
1. Open Performance Monitor in your Windows
*For WinXP you can open it by browse Control Panel > Administrative Tools > Performance
*For Windows Server 2003, you just click performance monitor in Administrative Tools in Start Programs
2. You can add more performance object monitor by simply click add button in menu bar (simbolize by plus button), so you can monitor your database performance.
Okay, we could use this feature now. But performance monitor is not a powerfull feature unless you understand about it's parameter to achieve good performance, right? Here some tips :
- Add memory object (Available bytes), check it's value! if your graph show more than 4Mb, well we can claim it as a good performance. This number should be low because Windows uses as much RAM as it can grab for file cache
- Add Processor object (%Processor Time), check it's value! The recommended value is less than 75%, if your graph show number higher than these, maybe you should add more processor, or do some tuning like partitioning and filegroup-ing and move some filegroups to other server.
- Add memory object (Pages/Sec), check it's value! The recommended value is fewer than 5. If this is too high, it means your system is compensating for a lack of RAM by paging to disk.
Well actually, there's another performance object you can use, but I won't explain all of those in here, because it'll be too time-consuming. These 3 object is commonly used by DBAs to monitor their server performance and their database performance. If your abilities in tuning your database is good, you can reduce your server workload, and also save your computer resources. :)
Display Estimated Time Plan
Yap, you also can check whether your database need indexing or not, by using Display Estimated Time Plan. Here the illustration :
Commonly, people use a normal table scan method for their tables. This method is used when we don't implement index or primary key in our tables. This method can cause more time when querying your database if your database is a VLDB (Very Large Database) and you have million rows in your table. So you need to implement index in your tables.
You can check estimated time plan comparison between table-scan method and index-method by using Display Estimated Time Plan feature. For example, try to check estimated time plan for your table with table-scan method. Check your database performance by use select query or more complex query to retrieve data in your tables. After the results is shown, select 'Query' in your menu bar, and click Display Estimated Time Plan. Move your mouse cursor to method's shown in there (it could be either table scan or index method), and it will pop up message contains Estimated CPU cost time. After that, try to implement index for your table/database. You can use Database Engine Tune Advisor or manually create index for your tables/database. You can compare the results before you implement index (use table-scan method) and after you implement index.
FYI : table-scan method isn't always be a bad method. It could be give a better performance if your database is small or just contains hundred to thousands rows in your tables.
Hope this post is usefull.
Regards,
Rangga Praduwiratna
*References : Sybex and Microsoft Press books for Implementing and Maintaining SQL Server 2005.
Apa yang dimaksud dengan Stored Procedure?
Stored Procedure merupakan struktur pemrograman yang cukup umum digunakan di dalam database administration dan development. Jujur saja, saya juga baru mulai mempelajari kegunaan dan pemakain fitur ini dalam pemrograman aplikasi. Sebuah stored procedure merupakan sebuah nama yang dapat diasosiasikan dengan batch dari kode pemrograman SQL yang disimpan di server database. Kita juga bisa menganalogikan stored procedure sebagai sebuah fungsi yang bisa dipanggil sewaktu-waktu, tapi jangan termind-set dengan istilah ini ya, karena di SQL Server 2005 juga ada fitur function.
Apa kegunaan Stored Procedure?
Begini ilustrasinya. Jika kita menggunakan query untuk mengambil data dari database (terutama untuk VLDB - Very Large Database) dan menggunakan beberapa query untuk menggabungkan beberapa tabel data tentu akan membebankan resource komputer server. Apalagi untuk kasus VLDB dimana besar kemungkinan bagi beberapa user mengakses data dengan query tersebut secara bersamaan.
Penggunaan Stored Procedure membuat hal ini menjadi lebih simpel dan mudah serta meningkatkan performance server. Stored procedure tersebut kemudian dapat kita panggil dari aplikasi user.
Nanti saya contohkan penggunaan stored procedure di SQL Server dan di pemrograman aplikasi menggunakan Visual Studio 2005.
Mumpung masih seger di ingatan, jadi saya mau sedikit membahas cara instalasi SQL Server 2005 Enterprise Edition 32-bit edition, karena proses instalasi yang baik (clean installation) merupakan sebuah kemampuan yang mendukung pekerjaan kita. Ok, pertama-tama kita harus tahu terlebih dulu OS yang compatible untuk aplikasi ini.
Berikut adalah daftar OS yang compatible untuk melanjutkan proses instalasi :
- Windows Server 2003 Standard/Enterprise/Datacenter Edition SP1
- Windows Small Business Server 2003 Standard/Premium Edition SP1
- Windows 2000 Server SP4
- Windows 2000 Advanced Server SP4
- Windows 2000 Datacenter Server SP4
- Windows XP Professional Edition SP2
Kemudian, minimum hardware requirement yang dibutuhkan untuk proses instalasi :
- Processor 600 Mhz, 1Ghz keatas lebih direkomendasi.
- Memory 512 Mb (minimum)
- Hard disk space : 350 Mb dan tambahan 425 Mb untuk instalasi SQL Server Books Online, Sample Databases, dan Sample Code
Nah, setelah mengidentifikasi kebutuhan OS dan hardware, maka proses instalasi dapat dilanjutkan.
1. Pertama-tama masukkan cd SQL Server 2005 Enterprise Edition, dan pilih proses instalasi
2. Pada halaman Installing Prerequisites Page, tekan tombol next.
3. Pada halaman Welcome to the Next Installation Wizard juga tekan tombol next.
4. Setelah ini, SQL Server akan mencek system configuration pada komputer Anda, dan menunjukkan komponen apa saja yang sudah ada dan belum ada untuk meneruskan proses instalasi. Jika ada komponen yang menunjukkan status warning, coba dicek terlebih dulu apa penyebabnya dan apa nama komponennya, mungkin Anda belum mengaktifkan component Windows tersebut.
5. Jika sudah selesai, maka Anda dapat meneruskan proses instalasi dengan menekan tombol next.
6. Isikan nama Anda dan nama perusahaan Anda di halaman berikutnya, jika sudah tekan tombol next.
7. Pilih komponen yang ingin Anda install di halaman ini. Integration Service merupakan fitur baru di SQL Server 2005 yang menawarkan banyak keuntungan dan kemudahan administrasi database. Jika Anda ingin bereksplorasi, aktifkan semua komponen. Kemudian pilih menu Advanced untuk menginstall sample databases, SQL Server Books Online, dan Sample Codes. Setelah konfigurasi selesai dilakukan, tekan tombol next.
8. Di halaman berikutnya pilih default instances untuk Instances Name Anda. Untuk catatan, Anda dapat menginstall lebih dari 1 instances, tapi untuk instalasi pertama kali, Anda harus menginstall default instances ini. Instances dapat diumpamakan sebagai server baru. Anda dapat mengaktifkan instances baru untuk masalah kompatibilitas dengan SQL Server edisi sebelumnya, testing software, dll. Tekan tombol next setelah selesai.
9. Pada halaman Service Accounts, pilih Local System, kemudian pilih tombol next.
10. Pada halaman ini, Anda harus memilih mode autentikasi yang ingin Anda gunakan. Bagi Anda yang baru pertama kali mencoba aplikasi ini, sebaiknya Anda pilih Windows Authentication Mode terlebih saja. setelah selesai, tekan tombol next.
11. Pada halaman Collation Settings, Anda dapat memilih mode collation yang ingin Anda gunakan. Untuk default, SQL Server akan menggunakan incase-sensitive,inaccent sensitive, dan sebagainya. Tekan tombol next jika sudah selesai.
12. Pada bagian error report pilih bagaimana sikap Anda jika ada error pada SQL Server 2005, tekan tombol next jika sudah selesai.
13. Jika sudah mencapai tahap ini, berarti Anda sudah siap menginstall SQL Server 2005. Anda akan diberikan review konfigurasi yang telah Anda pilih sebelumnya di halaman ini. Tekan tombol install jika sudah selesai.
14. Setelah selesai. Klik tombol finish.
Yap itu tadi step-by-step proses instalasi SQL Server 2005. Untuk berikutnya, Anda dapat menyesuaikan proses instalasi sesuai kebutuhan Anda. Selamat mencoba.
*Maaf, bahasa jadi campur aduk, Indonesia-Inggris. He2.. Masih gagap kalo pake Inggris buat nulis artikel begini. Nanti tutorialnya bakal dilengkapi dengan screenshot.
Rangga Praduwiratna
MCTS stands for Microsoft Certified Technology Specialist. It's a new generation certification which is released by microsoft (for more information, you can enter microsoft site). It has several advantages than the old credentials.
"The Technology Specialist certifications enable professionals to target
specific technologies and distinguish themselves by demonstrating
in-depth knowledge and expertise in their specialized technologies."
Yap, when you become an MCTS, you could show your expertises in certain technologies. It will help companies who are looking for new employees to find out your expertises.
8 May 2008
*Important Activity : Join as a new member of INDC
Hello World! It's a common message, when we try program. Don't know, when this 'magic word' become popular, but I'm still using it right now, when trying new programs or technologies. So, in this opportunity, I also use this 'magic word' to test out blog features in here and also say hello to all of you. I'm still a new member in here, but I hope I could give a lot of contributions to this community and to Indonesia.
Rangga Praduwiratna