September 21, 2008 at 1:24 pm
Which type of indexing to be used for large databases having more then 100 tables and a capacity to hold more then 10,000,00 records in the system ?
What type of security implications we need to impart for such types of databases ?
How to make sure that we dont have any mis matched entries in the database if more then one tables are interlinked ?
What is the best way to maintain the error log ?
September 21, 2008 at 1:36 pm
shishirbabel (9/21/2008)
Which type of indexing to be used for large databases having more then 100 tables and a capacity to hold more then 10,000,00 records in the system ?
That's not a particularly big database. You need to make sure that the indexes you create are useful for the queries that will run against the tables. To see if the existing indexes are used, you can run the queries that are typically used and view the exec plan to make sure that the optimiser is picking an index. You can also use the index usage DMV (sys.dm_db_index_usage_stats) to see which indexes are not used.
If the queries aren't using indexes if could be because they're badly written or because the indexes are inappropriate.
What type of security implications we need to impart for such types of databases ?
Not sure what you're looking for here.
Security should be set up (like for any database) to ensure that users have the minimum permissions needed to do what they need to do. No sysadmin for everyone because it's easy.
How to make sure that we dont have any mis matched entries in the database if more then one tables are interlinked ?
Make sure that the tables are normalised and that foreign keys exist to enforce referential integrity.
What is the best way to maintain the error log ?
The error log doesn't normally need to be maintained. SQL will cycle the error log whenever it restarts and will, by default, keep the current and the previous 6 error logs.
You can cycle the error log manually if you like - sp_cycle_errorlog
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2008 at 1:46 pm
Thanks for the quick response.
What my main point is that i am not sure that which indexing to use to make sure that database responds in a very fast and efficent manner.
Clustered Indexing/ Non Clustered Indexing which one to use ? Please suggest me the best way for the improvements in the database.
If you want i can give you the complete schema of my database so that you can check how efficiently it will work.
Please feel free to make the modifications in the database to make it more better and powerful ?
Please give me your mail id and i will send you the database if its fine with you ?
September 21, 2008 at 2:01 pm
shishirbabel (9/21/2008)
Thanks for the quick response.What my main point is that i am not sure that which indexing to use to make sure that database responds in a very fast and efficent manner.
Clustered Indexing/ Non Clustered Indexing which one to use ? Please suggest me the best way for the improvements in the database.
You can only have one clustered index per table. It's good practice for each table to have a clustered index. Depending on the queries you may need one or more nonclustered indexes as well.
The location of indexes is very dependent on the table structure, the data in the tables and the queries running against those tables. There's no golden rule for where to put indexes. There's a lot written on indexing and a google search will get you a great deal of information.
If you want i can give you the complete schema of my database so that you can check how efficiently it will work.
Please feel free to make the modifications in the database to make it more better and powerful ?
Please give me your mail id and i will send you the database if its fine with you ?
You can post the schema here if you want advice on it. I'm sure someone will be willing to offer a couple of suggestions.
Bear in mind that that the people who answer posts here do it in their spare time. If you want a complete review of your schema you should consider hiring someone to do that as it can take quite a bit of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply