SQL server ilk kurulumunda ve sonrasinda yapilmasi gereken konfigurasyonlarda yapilmasi gereken ayarlar: 1-SQL_Latin1_General_CP1254_CI_AS olmasina dikkat edersek Turkceyede uyumlu. 2-sistem databaseleri ayri diske 3-LDF dosyalari ayri diske 4-Temp database ayri diske birkac data dosyasi halinde 5-Database---> properties----- files dan---- growth buyut 40 MB mesela 6-sp_configure 'backup compression default',1; --backuplar compressler reconfigure 7-sp_configure 'max degree of parallelism',23;--acilamasina bak reconfigure 8-sp_configure 'max server memory (MB)',102400;--max nekadar memory kullandirmak istiyorsan o kadar ayarla reconfigure 9-sp_configure 'min server memory (MB)',2048;----min nekadar memory kullandirmak istiyorsan o kadar ayarla reconfigure 10-sp_configure 'fill factor (%)',90;--pagefile da bos birakilacak oran reconfigure 11-sp_configure 'max worker threads',512;--hesaplmasi aciklamalarda var reconfigure 12-sp_configure 'optimize for ad hoc workloads',1;--SQL2008 de var.Sunucunun plan cache kullanımı incelenmesi gerekir. Toplam AdHoc query planlarının yaklaşık %37 ‘si sadece bir kez kullanılmış ise, Bu durumde ‘optimize for ad hoc workloads’ değerinin 1 ‘e set edilmesi değerlendirilebilir. reconfigure 13-sp_configure 'xp_cmdshell',1;--command shell komutlarinin SQL de calismasi icin. reconfigure -------------------------------------- Alternatif konfigurasyonlar: SQL Server compiles query and saves the procedures cache plans in the database. When the same query is called it uses compiled execution plan which improves the performance by saving compilation time. Queries which are parametrized requires less recompilation and dynamically built queries needs compilations and recompilation very frequently. Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Database which has high volumes of the queries can be most benefited from this feature. When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement is converted to a parameter during query compilation. When the PARAMETERIZATION database option is SET to SIMPLE, the SQL Server query optimizer may choose to parametrize the queries. SELECT 'ALTER DATABASE '+ name ' SET PARAMETERIZATION FORCED WITH NO_WAIT' FROM sys.databases WHERE name not in ('master','msdb','model','distribution','ReportServer','ReportServerTempDB') ORDER BY name GO
