November 8, 2005 at 9:33 am
Hello Experts,
I'm having some doubts for suggestions and guidance.
1. I'm having some important databases which are needed to be available for the end users 24*7. In this scenario, how do I manage the DB maintenance plans which include backups (daily), transaction log backups (daily - 2hrs), integrity checks (weekly once). I know that SQL Server brings the particular database to single user mode for executing the maintenance plans and the users are complaining about getting disconnected during off hours especially between 1 am - 3 am. Some maintenance plans are scheduled to run around 6 pm - 10 pm and some are at 4am.
2. Every day I check up the event viewer - application log and find the flg message
This instance of SQL Server has been using a process id of xxxx since 11/4/2005 2:01:25 PM (local) 11/4/2005 7:01:25 PM (UTC).
Does this mean that the user process id xxxx has established a connection on 11/4/2005 and still continuing to have it as open? If so, does it have any performance issue on the server -> database?
3. I've read in some articles that for best performance, a select statement should be written as
Select (1) from <tablename> instead of Select (*) from <tablename> when no "where" clause is given with a filter condition. How can I convince my colleagues in this regard?
4. My company purchased a 3rd party diagnostic tool and it sends an alert as
The response time for server "aaa" is currently 5067 ms.
How should I tackle such issues.
For now these are the questions in my mind.
Thanks in advance for clarifications.
Lucky
November 8, 2005 at 10:39 am
Hi Lucky,
In regards to your #2:
It means that SQL Server itself is using a process ID on the server since it was probaly was restarted the last time. It does not have anything with users connecting to SQL Server. Open Task Manager. Click on Processes tab. Do View ->Select Columns. Check PID column. Click OK. Find sqlservr process on the Processes tab and see that its PID will be your xxxx from the message in the event log. These messages are normal and by design.
About #3: no one can tell a developer how to write his code. You can just try it yourself, create 2 demos with your syntax and with what they do and compare performance and also execution plans.
Regards,Yelena Varsha
November 8, 2005 at 10:42 am
1. The reason the maintenance plans put your databases into single user is because you have checked the "attempt to repair any minor problems" checkbox on the "Integrity" tab. I seldom check this for that reason.
2. This is just an informational message, it does not indicate a problem.
3. Yes and no. It is generally considered best practice to enumerate the columns that you want in a query, and in some cases, it can make a difference in performance, but this really comes down to a detailed knowledge of the table in question. The indexes that exist, and the data types of the columns would be the determining factors in whether or not there would be much if any performance difference.
But specifying the columns can help with data independence. Meaning that if the table definition changes the application wouldn't care (unless one of the columns it was requsting was dropped). But you could add columns or change their order without screwing up the application if you list the columns in your select statements.
4. I don't even know how to begin answering this one. Generally 5067 ms seems like pretty bad response time, but depending on the query (or how it's being measured) it might actually be pretty good.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
November 8, 2005 at 10:59 am
For Database and transaction log backups, the database is NOT in single user mode.
For integrity checks, the database will be in single user mode only when the checkbox "attempt to repair any minor problems" is TRUE. See Books OnLine for DBCC CHECKDB REPAIR_FAST.
Please note that if CHECKDB does indicate there is a problem, this is very serious and SQL statements will produce accurate results. Immediate repair is absolutely necessary and this means downtime. With SQL Server 2000, over the last 4 years, every database corruption problem I have seen has been caused by the disks/SAN. Before installing SQL Server, especially if using a SAN, run the SQLIOStress utility with parameters to make a short run time of about 1 hour.
Be sure to monitor the system event log and be immediately alerted of any hardware problems.
The "process id" refered to is the Windows process being used by SQL Server. This is an information message and can be ignored.
<
3. I've read in some articles that for best performance, a select statement should be written as Select (1) from instead of Select (*) from when no "where" clause is given with a filter condition. How can I convince my colleagues in this regard?
>
Please provide more details as you statement is unclear as to context.
Are you using BMC Patrol ? I would turn off this alert as it often will occur during DB maintenance and slower response time should be expected.
You do need to analyze historical resource utilizations for capacity planning including peak CPU usage, disk % busy.
SQL = Scarcely Qualifies as a Language
December 20, 2007 at 3:21 pm
3. I've read in some articles that for best performance, a select statement should be written as
Select (1) from when no "where" clause is given with a filter condition. How can I convince my colleagues in this regard?
It's pretty simple, just ask them whether they prefer to hop on a plane with a small carry-on bag, or check 5 suitcases weighing 200lbs! You are SQL Server and the bag/s are the columns.
Every column that you include unnecessarily represents a chunk of data that SQL Server must return to the client application - that eats up bandwidth and time. Also, including those unecessary columns completely changes how the query is optimized because SQL Server cannot look for shortcuts to get only the data from certain columns (to find out more search Books Online or Google for "covering index").
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply