|
|
SELECT [Whims_and_Fancies] from [my_SQL_Server_Knowledge];
-
Reconfiguring the hardware or software on the server is a big job and you wouldn't attempt to perform the task on day-to-day basis or even not in your life time.
Here is the detailed steps that are involved in this mammoth task refer to this post: http://sql-server-performance.com/Community/forums/t/29471.aspx in the forums.
A good one to learn....
|
-
Here is another Frequently Asked Question in the forum to find out the damage that has happened in your SQL Server environment.
To find out more on what has been referred review this Forum_Post link.
|
-
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 the issue will be they treat the SQL data file as a whole and will not defragment it granularly. You may be aware that FILE FRAGMENTATION for SQL Server data file occurs even when you perform a backup and restore of that database, in case of having multiple files for data & indexes. But to address this issue you cannot perform a downtime to SQL Server services in order to let the operating system defragmentation tool too defrag the physical file, and theoritically speaking there isn't much external fragmentation anyway. It's more helpful to regularly reindex your data to reduce the internal fragmentation as much as possible, that will reduce the outer layer of fragmentation of physicial file where the effectiveness of the read-aheads and the amount of data that can be buffered, to the maximum (as per my tests).
The efficiency of performance (resource level, such as Disk) will be efficient disk I/O, by making sure the disk alignment and RAID configuration is correct, scaling your disk arrays to properly handle the I/O load, and maintaining proper layout of the Log, Data, TempDB, and backup files. As usual the best practice of not letting the database with AUTO-SHRINK and leavintg the AUTO-GROW settings on the volatile inserts/update/deletes processes of database. There is a reason behind for not letting the default-setting of auto-grow (in case of auto-shrink too), as it will reduce the number of trips of volume-level file fragments created.
So for the newly created database it may not be much as the data and log files are initialized with empty space. In this case Data and log files are first initialized by filling the files with zeros when you create a database; add files, log or data, to an existing database; increase the size of an existing file (including autogrow operations); or restore a database or file group. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros. Within SQL Server 2005 version, the data file are initialized instantly, that will be capable for fast/quick execution of file operations. This can be achieve with an instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files, but not in the case of log files. Bear in mind such an operation (advantage) will be good at when using Windows 2003 server version and later.
So with using DBCC statements such as DBCC SHOWCONTIG the SQL Server will not report fragmentation, but will give 0 percent fragmentation even when the on-disk files are horribly fragmented. As the operating system level layout is not available to SQL Server, that also depends upon the physically lays out the bits on disk; so it's the operating system's job to manage physical bits on disk. In this case, performing an OS-level defragmentation could help performance by making the files more contiguous on disk. However, defragmenting at the OS level doesn't always have the effect that you might expect. SQL Server pre-allocates space when you create a file. It is best to perform a scheduling of regular OS-level defragmentation using the tools such as Disk-Keeper and http://www.diskeeper.com/defrag/dk-boost-sql-server.asp. So it is better to integrate the OS level jobs during quiet period on SQL Server as file defragmentation is integrated in the operating system, defragmenting open files is perfectly safe. However, I/O activity generated must be considered if continuous high performance is mandatory. To close the last piece of this blog I would like to suggest to create the data file on contiguous space that has already been defragmented, such as speicifying the next 3 or 5 year growth of your database, this is specific to OLTP based databases.
Above all you must ensure to continue these practices on a periodic basis to ensure the fragmentation is addressed efficiently, as it occurs from time to time and it is another best practice too.
|
-
You may have seen the sp_replwriterovarbin - New SQL Server Injection-Based Attack Found vulerability about the new public reports of a vulnerability that could allow remote code execution on systems with supported editions of SQL Server 2000, 2005, 2005 Express Edition, 2000 Desktop Engine (MSDE 2000), and Windows Internal Database (WYukon).
The only exceptional SQL Server systems are SQL Server 7.0 Service Pack 4, SQL Server 2005 Service Pack 3, and SQL Server 2008. MSA961040 highlights that this vulnerability is not exposed anonymously. An attacker would need to either authenticate to exploit the vulnerability or take advantage of a SQL injection vulnerability in a Web application that is able to authenticate and by default, MSDE 2000 and SQL Server 2005 Express do not allow remote connections. An authenticated attacker would need to initiate the attack locally to exploit the vulnerability, so it is always better to tighten the permissions on these databases. Review more information from this CVE reference link and the general recommendation from Microsoft Security Advisory that all customers should apply the most recent security updates released by Microsoft to help ensure that their systems are protected from attempted exploitation.
As usual the systems that are enabled Automatic Updates will get this windows update, if not you must refer to Microsoft Security Central site for more information. Here is the workaround referred by MSA961040 link : chkHide('s'+sID);
Use one of the following procedures:
| • |
To deny access to the stored procedure, connect to SQL Server as a sysadmin using osql.exe or sqlcmd.exe or through SQL Server Management Studio and execute the following T-SQL script: use master
deny execute on sp_replwritetovarbin to public
|
| • |
To deny access to the stored procedure using SQL Server administration:
| • |
For SQL Server 2000:
|
1. |
Connect to SQL Server using Enterprise Manager as a sysadmin |
|
2. |
From the SQL Server Enterprise Manager window, select the desired server |
|
3. |
Expand the databases |
|
4. |
Expand Master |
|
5. |
Click Extended Stored Procedures. A list of stored procedures appears. |
|
6. |
From the list of stored procedures, right-click sp_replwritetovarbin and select Properties |
|
7. |
In the Properties window, click Permissions |
|
8. |
Under Users/Database Roles/Public, find Public, then click the box in the EXEC column. The box turns into a red X. |
|
9. |
Click OK twice | |
| • |
For SQL Server 2005:
|
1. |
Connect to SQL Server using SQL Server Management Studio as a sysadmin |
|
2. |
From the Object Explorer window, select the desired server |
|
3. |
Expand the databases and the system databases |
|
4. |
Expand Master |
|
5. |
Expand Programmability |
|
6. |
Click Extended Stored Procedures. A list of stored procedures appears. |
|
7. |
From the list of stored procedures, right-click sp_replwritetovarbin and select Properties |
|
8. |
In the Properties window, click Permissions |
|
9. |
Click Deny execution beside the desired user IDs and click OK | | |
Impact of Workaround: Disabling the sp_replwritetovarbin extended stored procedure prevents updates to subscription tables by all users. The impact of this workaround only affects customers that use transactional replication with updatable subscriptions. Customers using transactional replication with read-only subscriptions, bi-directional transactional replication, or peer-to-peer transactional replication are not impacted. For more information on transactional replication with updatable subscriptions, see MSDN.
How to undo the workaround
• |
Connect to SQL Server as a sysadmin using osql.exe or sqlcmd.exe or through SQL Server Management Studio and execute the following T-SQL script: use master
grant execute on sp_replwritetovarbin to public
|
• |
To restore access to the stored procedure via SQL Server administration:
| • |
For SQL Server 2000:
|
1. |
Connect to SQL Server using Enterprise Manager as a sysadmin |
|
2. |
From the SQL Server Enterprise Manager window, select the desired server |
|
3. |
Expand the databases |
|
4. |
Expand Master |
|
5. |
Click Extended Stored Procedures. A list of stored procedures appears. |
|
6. |
From the list of stored procedures, right-click sp_replwritetovarbin and select Properties |
|
7. |
In the Properties window, click Permissions |
|
8. |
Under Users/Database Roles/Public, find Public, then click the box in the EXEC column. The box turns into a green checkmark. |
|
9. |
Click OK twice | |
• |
For SQL Server 2005:
1. |
Connect to SQL Server using SQL Server Management Studio as a sysadmin |
2. |
From the Object Explorer window, select the desired server |
3. |
Expand the databases and the system databases |
4. |
Expand Master |
5. |
Expand Programmability |
6. |
Click Extended Stored Procedures. A list of stored procedures appears. |
7. |
From the list of stored procedures, right-click sp_replwritetovarbin and select Properties |
8. |
In the Properties window, click Permissions |
9. |
Click Grant execution beside the desired user IDs and click OK | | |
As referred above these steps will reduce the problem by blocking the known attack vectors, as I have also successfully applied on the affected SQL Server systems at my end. For more information refer to this SQL 2005 Security best practices link and list of webcasts on security best practices:
|
-
One of the TRUSTWORTHY features within SQL Server 2005 will be like when you are running a stored procedure with a dynamic execution query (sp_executesql or EXECUTE), you may receive the following error message:
Server Msg 229, Level 14, State 5, Line 1 'permission' permission denied on object 'object', database 'database', owner 'owner'.
As a Developer or DBA you wouldn't get confusion when trying to execute the above piece of code 'sp_executesql' as it is specific there explicitly, how about when this returns within a stored procedure execution. It is obvious that few of the processes will be executdd under a user login which may have DBO permissions and such confusion can occur as all the tables are owned by dbo, and all stored procedures are owned by dbo.
Dynamic SQL is easy to get the things around when you do not have the direct permission on the tables, but sp_executesql has a different scope from the stored procedure it is called from, and it doesn't inherit the permissions from the stored procedure. So the statement inside sp_executesql is run with the permissions of the user who called the stored procedure, and that user might not have permissions on the table. Erland Sommarskog (SQL Server MVP) has given an excellent indepth of information about Curse-Blessings_of_DynamicSQL which is a definitieve guide for the Developers & DBAs and also I recommend to visit Fun_and_Fumes_DynamicSQL-Injection issues blog post that might affect the security of your database system.
Coming to the issue resoution either you have to remove all the permissions to EXECUTE sp_executesql on that SQL instance or grant SELECT permissions on the stored procedure. Due to the reason that this behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.
If you have deployed ownership chains then you must take the above approach into account while you are performing the process to grant the necessary permission for each underlying object that is mentioned in the dynamic execution query.
|
-
Microsoft press announces that SQL Server 2008 is available for users and as per PressRelease :
...announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company’s acclaimed data management and business intelligence platform. This version of SQL Server provides powerful new capabilities such as support for policy-based management, auditing, large-scale data warehousing, geospatial data, and advanced reporting and analysis services. SQL Server 2008 provides the trusted, productive and intelligent platform necessary for business-critical applications.
It is indeed a great news for the SQL Server users about 2008 RTM, as their journey begins since last year from CTP4 onwards from this CTP-Post & [SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet?] here.
So now the question is about number of editions that are available, it will be (as per BOL):
SQL Server 2008 Enterprise. SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.
SQL Server 2008 Standard. SQL Server 2008 Standard is a complete data management and business intelligence platform that provides best-in-class ease of use and manageability for running departmental applications.
SQL Server 2008 Workgroup. SQL Server 2008 Workgroup is a reliable data management and reporting platform that delivers secure, remote synchronization and management capabilities for running branch applications. This edition includes core database features and is easy to upgrade to the Standard or Enterprise edition.
SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.
SQL Server 2008 Developer. SQL Server 2008 Developer allows developers to build and test any type of application with SQL Server. This edition features all of the functionality of SQL Server Enterprise but is licensed only for development, test and demo use. Applications and databases developed on this edition can easily be upgraded to SQL Server 2008 Enterprise.
SQL Server 2008 Express. SQL Server 2008 Express is a free edition of SQL Server that features core database functionality including all of the new SQL Server 2008 data types, in a small footprint. This edition is ideal for learning and building desktop and small server applications, and for redistribution by ISVs.
SQL Server Compact 3.5. SQL Server Compact is a free embedded database designed for developers and is ideal for building stand-alone and occasionally connected applications for mobile devices, desktops and Web clients. SQL Server Compact runs on all Microsoft Windows platforms, including the Windows XP and Windows Vista operating systems, and on Pocket PC and smartphone devices.
As Microsoft keeps its words about costing on deploying this product, in terms of licensing as per License_pressrelease information.
So whats the catch on the downloads, it is now available to MSDN and TechNet subscribers and will be available for evaluation download from Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download now FromHere.
|
-
I found this interesting question within SSP forums:
Is it possible to return only the upper case of a given string. For a string say, 'My name Is John Cramps' I should get the output as 'MIJC' I am using SQL Server 2005.
Visit forum http://sql-server-performance.com/Community/forums/t/27634.aspx post for more information & excellent code references.
|
-
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' practices (bad) that are seen at most of the deployments by leaving the blank password for any application connectivity or very common used words such as 'password' or so. In this fashion any generic installation of SQL Server and can be relatively easily configured on the server, database, or database object level and obvious attempt on access to data is provided via client applications, which increases the range of potential vulnerabilities and places an equal share of responsibility for data security on software developers, where I feel most of users must be educated/trained on security aspects.
So to know more about SQL injection attacks and vulnerabilities within your system I'm providing few best examples and explanation, such as SQL Injection attacks post by Buck Woody & SQLInjection-Attachs-by-example blog posts.
To close the topic I would like to highlight the importance of monitoring the information (small or big) that might be revealed via error messages resulting from executing malformed SQL statements. It is like leaving your house key to the door lock when you are supposed to secure it when you are going away!
|
-
This is the question:
I am configuring a server with Windows Server 2003 Standard Edition and SQL Server 2000 Enterprise Edition. The server has 4GB memory. From what I have read the AWE/PAE switch only works with Advanced Server or Datacenter Editions, which are not an option for me. What is needed for SQL Server to use 3GB of the memory for 2003 SE?
Solution: Refer to the forum post - http://sql-server-performance.com/Community/forums/t/14372.aspx in this regard.
|
-
I would like to refer this Interesting thread that talks about advantages you can take with Profile when you have a need of refactoring the database objects between old & new versions, useful tips on the post too.
|
-
Here is another interest forum post that will talk through the best practices, gotchas and FAQ when you are performing a database upgarde from SQL Server 2000 to 2005 version.
Post link.
|
-
One of the forum user reported about an error they are getting when trying install SQL Server 2000.
By default you need to have Administrator rights on the server where you are trying to install the SQL Server binaries, in this case the user has used the login with Domain Admin and local admin rights, still the error persists.
For resolution refer to the RelevantForum post itself.
|
-
One of the intersting post in forums and replies to them:
I recently took a new position as the DBA for a web app company. Getting to understand the performance issues that are a major concern for the company I have started monitoring the servers. The DB server so far does seem to be a little bit overloaded and I am working on reducing the workload. One of the counter (Full Scan/Sec for SQL Server Access Methods) is running way above that it should be. I am recording averages of 150-200 Full Scans per second and Max has been over 650. At the same time the number of active temp tables which we use a lot of in the procedures begin used averages around 1200. Since none of the temp tables (create @tablename) use indexes I was wondering if the tablescans could be due to the heavy use of the temp tables. I have not been able to find this information any where in the books on line, Microsoft knowledge base or on 4-5 websites I have searched so far.
MichaelB replied that: I would agree with your assumption. The tablescans could be related to the use of non indexed temp tables if they are used exnsively (which is not a good thing in itself). You can test this by checking the full scans/sec when running one of the queries that create the temp tables. Also, you can change some of that code to use derrived tables or table variables which should help out a lot or by adding correct indexes to the temp tables. Remember that table varaibles should not be used on large datasets. Indexed temp tables are better for that.
Further I have added to Michael's response as;
One of the documentation refers: Defined as the number of unrestricted full scans. These can either be base table or full index scans.
There is no good ratio that fits all situations. The more index you have, the less full scan you need, but your updates become slower. Finally, a high number here shows either poorly written stored procedures or bad indexing. Either way, you need to get to work identifying the source of the problem. See this blog post; http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx about how you can dig out the performance issues.
More to come....
|
-
This is anothe FAQ within the forums about what is the major difference between 32 bit and 64 bit application, well the answer you get is Memory.
I feel there is much more associated between them, follow this ForumDiscussion topic for more information.
|
-
A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources on the server such as memory, CPU etc.
Should you configure each of the instances to use specific portions of the available Operating System resources then best to leave the default settings, for instance dynamic memory settings on SQL Server. As per the configuration of SQL Server you can easily mix versions of SQL Server (both 2000 and 2005) on the same machine running Windows server, such as 1 default instance (either SQL Server 2000 or SQL Server 2005) plus everything else as a named instance (SQL Server 2000 or SQL Server 2005) up to the number of supported instances for that version of SQL Server, or all named instances for everything. On the other hand for the Clustering basis configuration this is exactly the same—you can have only one default instance plus the supported number of named instances for that version of SQL Server on a cluster, or all named instances.
Then coming to the Resource Utilization the answer is it depends. Say if you have 4 multiple instances and there isn’t any resource contention and there is enough of each resource to go around, then there is usually no reason to set any caps or limits. The Operating System and SQL Server will share the available resources without any problems. But when you find out that there is a resource contention, then depending on the resource and what you want to happen, the way that SQL Server and the OS manage the contention might not give you the result you want. In this situation you should consider setting caps on the resources in dispute.
The default setting of SQL Server dynamic memory settings when each SQL Server instance will take what memory it thinks it needs, up to the available physical memory. When there is memory contention, then sometimes one SQL Server instance won’t release memory as quickly as you might like. If you are in this situation you may need to configure maximum and minimum server memory settings to manually control this. As these are dynamic settings in SQL Server 2005, you can change them without a reboot and see an immediate effect.
Also having the Anti Virus software installed on the servers is a common placement and in this case make sure that if it’s not cluster-aware, can have strange effects and interact in a negative way with clusters. Be sure to check with your antivirus vendor to see if it is cluster-aware or not, refer to AntiVirus-SQLServer post as well. Ensure to have a complete understanding of when a full and incremental antivirus scan is performed during the maintenance window for a database server is essential. Typically these scans run in the same timeslot as the maintenance window. To avoid resource contention, you might work with the infrastructure support teams to lock down backups, antivirus scans, software patches, and other schedules. Then you could look at server activity over a 24-hour period to see user time, processing times, and maintenance window times.
So keeping this in mind whenever required the operating system and SQL Server instance will have a great job of sharing the CPU between all threads. Better to collect the statistics during busy times & less busy times using SYSMON tool, this will get you much information. Think about a server with one instance running a decision support system (DSS) with a few very complex long-running queries, and another instance running an online transaction processing (OLTP) system. The OLTP system needs an unfair share of the CPU to allow it to guarantee response times to its users. You have to make sure the DSS instance doesn’t consume all the resources and starve the other instances while executing its complex queries. In a clustered environment, there may be additional factors concerning what happens when an instance fails over. In a multiple instance failover cluster configuration you have to take into account not just the resource needs of the instances running on this node, but also the resource requirements of the instances that may fail over to this node. This is a more complex topic and is discussed at length in the white papers located at "SQL Server Consolidation on the 32-Bit Platform using a Clustered Environment" and "SQL Server Consolidation on the 64-Bit Platform".
|
More Posts Next page »
|
|