September 28, 2011 at 7:51 am
Hello Friends, I am working on a SQL review process and need to prepare a document which will help us to point out any possible Hardware or Performance issue before implementing anything new related to SQL into the environment. For example –if we are creating a new Table or Database what are the things we should consider or if we are creating a new procedure or updating an existing one.. what are the things we should consider or if we have any performance related issues.. where to start from? What are the important things we should consider for performance improvement and so on… I am kind of new to all this and I would really appreciate your help on this---Please help
September 29, 2011 at 9:47 am
What you really need to do is setup some coding standards so basically a document that details what should happen when creating objects.
For example. If your system was predomenantly a read based for a large number of users:
1. limit locking within read stored procedures by using transaction isloation level or with (nolock) hints.
2. Avoid the use of functions within the where \ join clauses as this can render indexes useless.
3. Ensure naming standards are always the same so for example tables are always preceeded by tbl_ reaqd procs are procededded with spr_sel_... update sprs preceeded with spr_upd_... etc.
4. Avoid the use of dynamic sql within procedures
5. Avoid the use of iterative loops, i.e. Cursors, while loops etc.
6. Ensure all stored procedures return results within X seconds
7. Ensure all queries use the correct indexes to avoid scans.
8. Ensure all tables have a Primary Key
I could go on all day.
The coding standards document in my organisation is almost 100 pages long and stringently enforced. You should also implement code reviews. This is generally done by peers to ensure the best methods are used.
MCITP SQL 2005, MCSA SQL 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply