Today, one guy asked me a question. He has a SQL server (2000) with 2GB of RAM and 4 procs. There are about 400 client machines running on "Windows Me" who connects to the server daily. The problem is, he is getting escalations from the end users, that when they connect to the computer, it responds slowly and occasionally return error messages. It has been confirmed that it is not a network issue.
He has checked the processor performance and RAM utilization using system monitor and saw an average counter of 92% for %Total Time and %user time. Also %privileged time counter averages 20%.
The available MBytes counter is showing 500-600MB. He needs to improve performance for the users who logon to the system. Now what should he do to improve the performance?
Should he
a) Increase Physical RAM?
b) Increase virtual RAM?
c) Configure the processors and by which way?
Can you help out the guy?
It has been long time since I have written my first blog. I accept that it was messy and bad, but still it was my first entry on web, so please excuse :)
Anyway, meanwhile I was very busy with keeping the servers available at all time, I do find a little time to read some articles and check some of the tech-ed sessions on SQL Server 2005 held at Orlando (Advanced Querying Techniques, Tips & Tricks using Transact-SQL) and I was totally mesmerized by their presentation technique. Man, I have attended quite a few tech sessions in my life, but it was awesome. The most interesting part was David Rozenshtein's method of crosstab query.
They did show the query execution plan of a normal sub query which computes the sum of sales amount (Sales Qty * Unit Price) for each month. It was kind of 3 times faster in this type of Boolean sub query.
Check out Stephen's blog at http://www.stephenforte.net/owDasBlog/
Also, do check out his presentation on .NET Rocks! at http://www.dotnetrocks.com/default.aspx?showID=165
The motto of their presentation is you enjoy while learning. So, do not miss any of their sessions next time if you had a chance to attend!
I got hammered! Hammered big time!! It was surely my fault but did not understand that, since I was only a baby in this huge and ever changing industry! Let me whine about my boss … blah blah blah blah… That !@$*%^ doest not understand…
My first job was to test a huge warehouse where data comes from different feeds and get stored into different servers. I had to test some data and had to join different columns coming from two different servers. There were two option on my hand … To have a linked server or to DTS the table into one of the server and test it. I could not create a linked server as it was against the policy, so I opened the DTS wizard and happily transformed the table to another server’s master database!
Testing went OK.
On Friday I received a mail on my inbox. It was sent from my boss’ boss’ boss, Cc ing my boss. The content was like: “I see these tables were created by you in the master database of this server (screenshot attached). Can you please confirm that you have created this table?”
I don’t want to remember the rest.
So what is that in master, or broadly, what are in these databases? Why does Microsoft have put silly names like master (there should be one slave too!)?
So, I went to Books Online (BOL) and searched by database.
After reading the page I was willing to kick myself for putting things in master!
Here is a brief description of the databases available with SQL Server 2000 and their activities. I hope this will be helpful for those who want to work with SQL Server for the first time and/or aspiring DBAs.
Databases: 2 types which ships with Microsoft SQL Server 2000 developer edition:
- System Databases
- Sample databases.
System contains 1. master 2. model 3. msdb and 4. tempdb.
Sample contains 1. pubs 2. Northwind
We will discuss about the system database here.
Master:
It holds all the system tables. Altering those may corrupt your SQL Server instance and may require a fresh installation. The most important thing in it is the system catalog that keeps track of many things including disk space, login accounts, usage etc. Do not mess with this database and always keep a current backup of this database. Make a practice to backup the database whenever you are creating any new login or taking backup of a database if you are a DBA.
Model:
It is useful whenever you are creating a new database. It keeps a template and uses it whenever a new database is created. Hence a good idea is if you require some common features (like permissions) to be inherited in every database, you can put those into model and those will be automatically inherited by the new database.
MSDB:
This database is used by SQL Server Agent service. SQL Server agent can be utilized for various database maintenance plans. If you want to start a job whenever SQL Server starts, you may utilize this agent. It also stores the backup history logs, but do not get too much enthusiastic this database either.
You should be knowledgeable enough to get your hands into these three databases.
TempDB:
This is a temporary storage place for SQL server. Place your intermediate data or calculations here, but do not place any important table in it for future requirement. It is re-created every time SQL Server starts.
You can do whatever you want with this database, but do not expect any data created by you after a re-start. You can not recover this database.
Private tables will be created with a # prefix and global tables with ## prefix.
This being said, I am concluding today’s blog. Please let me know your comments and how do you feel about the way it had been expressed. Will it help the new comers? I am planning to explore more and more on topics which I have only touched.