SQL Server Security, Performance & Tuning (SSQA.net)

CLR Stored Procedures - handling security on SQL Server side

2008-07-24T08:52:00Z

Ok this is not new to say you can write stored procedures in any language to manage them in SQL Server, as 2005 version has started this CLR integration functionality that is exposed in an assembly called system.data.dll, which is part of the .NET Framework. So think about code access security in addition to managing security privileges for the user on database side. CLR supports the access security mechanism which is based on the assumption that the runtime can host both fully trusted and partially...(read more)

Microsoft Security Bulletin Summary for July 2008 - what is it now?

2008-07-09T08:07:00Z

I believe it has been a while that we have seen the security hotfix from Microsoft Security team on SQL Server (alone). The latest security bulletin has announced important security patches that are related to SQL Server in addition to Windows operating system. They quote that "... With the release of the bulletins for July 2008, this bulletin summary replaces the bulletin advance notification originally issued July 3, 2008. For more information about the bulletin advance notification service, see...(read more)

SQL Server: PERFMON counters to identify server utilization data

2008-06-18T12:21:00Z

Recently I have been involved to carry over an assesment of server utilization on the SQL estate farm (nearly 150+) instances, as it stands it is not an easy job to obtain the results when you have no influence on accessing required information on a shared platform. So in order to get the results quickly as you might guess using PERFMON *(SYSMON) tool is best to go with for initial assesment. As it defines within the SYSMON tool getting Collection of counter logs with Performance Logs and Alerts...(read more)

SQL Server - Precision Performance techniques using RML Utilities

2008-06-09T12:50:00Z

As a DBA, you probably often find yourself striving or struggling to improve the performance of SQL Server instance queries (might be smaller or complex ones). By default in order to get further analysis on the system's performance you need to perform server side trace along with SYSMON (PERFMON) trace collection, this is important in trying to discover the queries or batches that take too long to run, perform too many I/O requests, or use too many CPU cycles. By default the users will find using...(read more)

SQL Injection attacks - don't forget to visit guidance information from Microsoft

2008-05-31T20:59:00Z

One of the biggest threats in IT industry & Database world is unprecedented attacks aka most commonly termed as 'SQL injection'. There is no doubt that biggest database vendors, one of them as Microsoft so far providing recommendations regarding security-related configuration settings since the good & bad times of SQL Server version 2000, not in particular to point DBA or Developer and whole as in Application database, remember ' Slammer Worm '!. Here it is best to refer the ignored 'best'...(read more)

SQL Server 2005 Connectivity, Security policies & Network protocols: simple practice to follow, after installation?

2008-05-23T08:44:00Z

Secured by default, secured by design is the buzzword and catchy stuff for any Enterprise IT administrators. Then it comes about standards and policies to follow such as ISO , with the recent threats (last 10 years) and vulnerabilities within IT world it is most important factor you need to follow as per the compliance standards that are deemed as industry proven, Common Criteria. This 'international' standard has been ratified in the year 1999 that was designed by a group of nations to improve the...(read more)

SQL Server Performance issues with Fragmentation and heavy usage of TEMPDB?

2008-05-15T08:59:00Z

Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server 2005 (onwards). What to detect? Use SYSMON to detect excessive compiles and recompiles. Technical documentation refers that for this kind of problem use SQL Statistics object that provides counters to monitor compilation and the type of requests that...(read more)

SQL Server - Kill a KILLED/ROLLBACK status process without restarting Server or SQL services?

2008-05-06T08:02:00Z

This may be the typical situation within your database environment where the you might have killed a SPID (Process) that has been running for long time, without knowing the ROLLBACK operations for such processes. Say if you are executing a stored procedure which is built with a transactional based statements, by default the relational engine has the ability to control transactions mainly when you have specified a transaction starts and ends. In addition to that it must also be able to correctly handle...(read more)

SQL Server 2005 Login Security - CHECK_POLICY and CHECK_EXPIRATION setting, how it affects the password policy?

2008-04-30T08:53:00Z

Adding password complexity and password expiration to SQL Server logins is one of best feature I quote for SQL Server version 2005 onwards, similarly such password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced and password expiration policies are used to manage the lifespan of a password. In any of the above cases the SQL Server engine runs the check to enforce password expiration policy and...(read more)

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?

2008-04-25T08:30:00Z

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problems...(read more)

SQL Server latch waits - index contention & performance issues how DMVs can help you to get information?

2008-04-17T10:06:00Z

What kind of performance issues you see on day-to-day basis within your environment? The following elements are important factors that can add fuel to the fire (problem): Faulty hardware • Hardware that is not configured correctly • Firmware settings • Filter drivers • Compression • Bugs • Other conditions in the I/O path I/O is important factor to consider when it comes to the performance issues, such as blocking, locking and slow response time that will cause stretching of resources on the server....(read more)

SQL Server problem: Excessive paging and memory bottlenecks

2008-04-16T07:02:00Z

Paging and Memory bottlenecks are quite common when a performance problem strikes on your SQL Server. As we discussed here previously and relevant blog posts below talks about them: Paging and Available Memory for Operating sysem, what you need to know? SQL Server Memory - what's your method on Configuration and Troubleshooting Issues Using PERFMON to get memory counters, what you need to check? By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources....(read more)

SQL Server 2005 Scalability and Performance - recommended limit on number of instances on a clustered environment?

2008-04-04T09:48:00Z

This is a very tricky question and hard to stick to 1 or few solutions as answer, it depends! Overall the say is if there isn’t any resource contention (from your pre-installation testing & analysis) and there is enough of each resource to go around, then there is usually no reason to set any restrictions on the server resource. The Operating System and SQL Server will share the available resources without any problems. However, if there is any resource contention, then depending on the resource...(read more)

SQL Server Memory - what's your method on Configuration and Troubleshooting Issues

2008-04-01T09:49:00Z

Memory - an important aspect of system performance within a RDBMS platform, not specific to a database product or application. Coming to Microsoft related products such as Windows Server and SQL Server so on, various resources available on web such as MSDN blogs, Books Online and articles, frequently viewed concept for memory is such as /3GB switch and optimum memory settings. In any case the performance will be sufferred if there is a memory shortage, adding fule to the situation with insufficient...(read more)

What's your practice on Disk Defragmentation methods - specific to data file and indexes drives?

2008-03-26T08:01:00Z

Fragmentation is dearest friend of database when you need to deal with Performance, so by using the DBCC statements and other methods here you can deal the database level fragmentation, what about the physical level fragmentation? So what is the best way forward to fix the data file fragmentation in SQL Server, say when you have much of SHRINK operations are performed on Database level. The generic answer would be to use the defragmentation tools from windows or operating system based tools, but...(read more)