Returning to DBA work, few questions...

Question

Post reply

Returning to DBA work, few questions...

Keyser Sozay

SSC Veteran

Points: 214
More actions
March 17, 2004 at 9:36 am

#163309

Hi, I've recently come back into DBA work after a long time working outside the industry, I
used to use 7.0 and came across a few issues:
1) I've heard of something called 'Dynamic SQL', and understand it's mostly used in apps
that access Oracle DB's. Is it ever used in/with SQL Server?
2) When you import DB's from other sources, is there a way to import their individual
schemas? I vaguely remember it was possible, but you had to import the data as well - I'm
just not that certain any more.
3) Is there a way I can get SQL server to show me whether any queries are running poorly?
Perhaps by sending me an alert?
4) I remember you can add indexes to heavily queried columns to improve speed, but is there
anything else I can do to speed up SQL Server without re-writing the SQL code, or expensive
hardware changes?
5) Easy one (I think) - If I want to read the source code for a Stored Procedure in SQL
Server, how can I view it without running it? I think this was possible in Enterprise
Manager, but I won't be able to get to a system until Monday to take a look. Can you also
do it in Analyzer?
6) Do you remove the BuiltinAdmins account? I seem to remember it was a security risk in
7.0, but when you removed it, you couldn't schedule jobs.
Sorry if these questions seem a little basic, but it'd be great for me to catch up!
Thanks in advance,

Keyser.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

1: yes

2: if you get a script of the schema, you could import that without data. Kind of a broad question.

3. Profiler can help find/filter queries taking longer than xx. Might be able to integrate this with an alert.

4. Check statistics, maybe recompile procs. Revisit indexes to see if they make sense. Again, broad question. Memory is often a cheap solution that helps.

5. QA in SQL 2000 can. Object Browser. Course, you can always check syscomments.

6. You can remove it. Not sure it's a security risk, but it is another attack vector. There are ways around things.

4.

Keyser Sozay SSC Veteran Points: 214 More actions · Answer 2

Hi Steve, thanks for the replies, just a few points thereon:

1 Does SQL Server need to be specifically configured for dynamic queries?

2) Would SQL Server generate the script file, or the source database?

3) Other than Profiler, any others - could this be done in Perfmon?

4) Not sure which statistics you mean!

5) Could you confirm whether or not I can view SP's in EM?

Thanks again,

KS.

Manny SSC Veteran Points: 290 More actions · Answer 3

Here are some of answers to your questions:

1. Yes

2. Yes using DTS you can only import their schemas only, can do both schema and Data as well. I have done this myself email me for more info.

3. There are many monitoring packages out there that will help you with this problem.

4. Run regular maintenance on your database, and rebuild your indexes as least once a week. Run the following statements every night

DBCC CHECKDB('Database Name Here')

DBCC NEWALLOC

DBCC CHECKCATALOG

DBCC UPDATEUSAGE('Database Name Here')

5. Use SQLDMo for this one, I don't remember the commands of by hand but I have the source code at home and can look it up for you if you like.

6. Yes remove the built-in admins account, unless your admins are a bunch of power hungry p**cks that don't want to be taken out of the loop and will complain to management if you do then don't.

Let me know if you need anything else.

Cheers

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Slightly different take than Manny.

1. SQL Server doesn't have to be configured. Your code is what calls dynamic SQL. This is SQL that is built on the fly and submitted to the server as opposed to code that is set in a stored procedure.

3. Most monitoring packages are fairly $$$. Perf mon will not give you the specific queries that are running long.

4. In addition to above, UPDATE STATISTICS (look it up in BOL) will ensure these are up to date.

5.You can view SPs in EM

6. I leave Admins on the server do they can act as remote hands if I need them. They know enough to stay away from my servers.

Manny SSC Veteran Points: 290 More actions · Answer 5

Steve,

Hey I just wanted to clarify a few things.

1. When I said yes I was answering this question:

I've heard of something called 'Dynamic SQL', and understand it's mostly used in apps

that access Oracle DB's. Is it ever used in/with SQL Server?

not this one

Does SQL Server need to be specifically configured for dynamic queries?

3. There are lot of large corporations who can afford to spend money on monitoring packages. If you are taking care of over 30 or 40 servers it's well worth your while to buy a monitoring package as monitoring systems by hand at that point can very difficult.

4. I agree with you there.

5. What I meant was in addition to EM you can also use SQLDMO to view the script for stored procedures. Of course you can use EM to view the source for any stored procedure in SQL server.

6. Yes in most cases I leave the built admin accounts on.

However there is a passage in admin911 SQL Server 2000 written by Brian Knight that states the following

The default login for SQL Server is called BUILTIN\Administrators in the Logins group.The login could cause harm to your system and should be deleted or be given limited access.

Otherwise I agree with all your other answers.

Cheers

Keyser Sozay SSC Veteran Points: 214 More actions · Answer 6

Manny,

If you could give me a rough overview of how you can import schema in DTS, that'd be grand!

KS.

Manny SSC Veteran Points: 290 More actions · Answer 7

Keyser,

That depends what would you like to do ? Port a schema from an existing SQL Server Database or from another Databse such as Oracle or DB2 ?

Please let me know

Cheers