Designing a database
Not what you're looking for?
It is important to know the type of computer for running the database in order to ensure that the configurations of the computer are not too obsolete for the database to perform efficiently and properly. It is important for organizations to keep their hardware resources updated in order to maximize the usage of database management system. The knowledge of important features such as disk space available, processor speed etc. are prerequisites for installing database management systems. If the computers of an organization are to old, the database management system may not be installed in the organization and will not run properly or sometimes not at all. This information is good to remember as planning a particular database implementation.
I need a good description and explanation of any situation(s) where this type of situation hindered your project from successfully deploying and what needed to happen in order for the database to run at a proficient level?
Purchase this Solution
Solution Summary
It is explained that the knowledge of important features such as disk space available, processor speed etc. are prerequisites for installing database management systems. If the computers of an organization are too old, the database management system may not be installed in the organization and will not run properly or sometimes not at all. This information is good to remember as planning a particular database implementation.
Solution Preview
We had a situation at our main installation site when SQL Server was not able to process remote queries in real-time because of:
1. Computer Configuration was out of date
2. SQL was not properly configured to take advantage of performance tuning tools.
We did upgrade the computer hardware.
And we applied SQL performance tunning features to take advatange of SQL's built-in performance tuning capabilities.
How to Take Advantage of SQL Server 2000 Performance Tuning Tools
--------------------------------------------------------------------------------
SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:
Query Analyzer
Profiler
Index Wizard
System (Performance) Monitor
In the next couple of sections, we will take a look at how your can take advantage of these tools to help optimize your SQL Server-based applications.
SQL Server 2000 Query Analyzer
The SQL Server 2000 Query Analyzer is not only a great tool for developing and debugging Transact-SQL code, it is also a great tool for performance tuning Transact-SQL code. In this section we will take a look at what the Query Analyzer can do, and also learn a little about how it can be used to help identify and resolve performance problems. And like many of the more advanced SQL Server 2000 tools, you need to have a fairly good understanding of Transact-SQL to get the most out of this tool. Let's take a look at some of the key features included with Query Analyzer that can be used to help performance tune Transact-SQL code.
Show Execution Plan
Whenever you enter a query into the Query Analyzer, you can run it and see the results immediately. While this lets you know if the results are what you expected, it doesn't tell you much about performance. Sure, the bottom of the screen tells you how long the query ran, but that's about all.
One of the most powerful features of the Query Analyzer is that you can turn on a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server's Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.
The execution plan displayed in the Execution Plan window may be very simple, if the query is simple, or it may be very complex, if the query is complex. It will show you, step-by-step, how the Query Optimizer executed the query. The execution plan should be read right to left, as the right part of the plan indicates the first step taken by the Query Analyzer, and you continue reading the plan, from right to left, until you get to the left side of the screen, which indicates the very last step taken by the Query Analyzer.
While the graphical representation of the execution plan is interesting, the most useful and powerful part of this tool is somewhat hidden. If you move the cursor on top of each of the steps in the query plan, a pop-up box appears with detailed information exactly what the Query Optimizer did in each step as the query was executed.
The information displayed in the pop-up box is sometimes obvious, such as telling you that a "Clustered Index Scan" was performed, or it may very obscure, such as the "Subtree Cost" was .0376. The details provided not only often need further explaining, they need interpreting. The explanations can be found in the Books Online, but the interpretation takes experience using and performance tuning SQL Server, a topic beyond the scope of this article.
If the query you are working with takes a long time to run, and you want to experiment with your query, you don't have to actually run the query each time. Query Analyzer also has an option to create and display an Execution Plan without actually running the query. This option is also available under the Query menu, and it is called "Display Estimated Execution Plan".
When this option is selected, the Query Optimizer creates and displays the execution plan, but does not actually run it. Notice that this is referred to as an "estimated" execution plan. This means the resulting query plan may not be the exact one the Query Optimizer will use if the query is really run. It will be close though, and it is a good tool if you are doing a lot of experimenting with long running queries. Once you have the query fine-tuned using this feature, you can turn it off and then run it for real, and see how it actually performs.
Show Server Trace
The Show Server Trace can be used to help performance tune queries, stored procedures, or Transact-SQL scripts. What it does is display the communications sent from the Query Analyzer (acting as a SQL Server client) to SQL Server. This is the same type of information that is captured by the SQL Server 2000 Profiler, described later in this article. The main difference is that you only see the communications to SQL Server that are instigated by the query or script you are running in Query Analyzer.
The Show Server Trace feature can be turned on from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are displayed in a new window available from a tab that appears below the query results window on the screen.
The results of the trace are in the form of rows, with each row representing a distinct communication from Query Analyzer to SQL Server. Each row includes the text of the communication, such as Transact-SQL code; the Event Class, which describes the type of communication being sent; the duration of the communication; the amount of CPU time used, and how many reads or writes that were performed for the event. This information can be very valuable when analyzing query performance, and when comparing the performance of one variation of a query against another.
Show Client Statistics
Like the Show Server Trace feature, the Show Client Statistics can be very helpful when performance tuning queries, stored procedures, and scripts. What this option does is provide you with application profile, network, and time statistics of whatever Transact-SQL you are running in Query Analyzer. This statistics provide additional information you can use to see how efficiently a query is running, and also allows you to easily compare one query against another.
The Show Client Statistics feature can be turned on from the Query menu on the main menu of Query ...
Purchase this Solution
Free BrainMass Quizzes
Excel Introductory Quiz
This quiz tests your knowledge of basics of MS-Excel.
C# variables and classes
This quiz contains questions about C# classes and variables.
Word 2010: Tables
Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.
Basic UNIX commands
Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.
Java loops
This quiz checks your knowledge of for and while loops in Java. For and while loops are essential building blocks for all Java programs. Having a solid understanding of these constructs is critical for success in programming Java.