|
Hi folks,
I have a database in MS SQL Server which is accessed by an application I wrote. In other words, I have control over both the DB and the software. Things have grown so much now that I'm getting worried I might not have set up all the correct indexes in the DB to really optimise the queries.
I should probably sit down and go through all of my code to see what queries are being run against the DB and what indexes would optimise those queries but there is such a multitude of queries and it would be very difficult to determine which of these are run frequently and which of them only spradically.
Is there a tool that can monitor all the queries that are being run against the DB over a period of time and then analyze the shortcomings in the DB design? Somehow I recall having heard of such a tool but I've never used anyhting of the sort and don't really know where to start looking.
|
|
|
|
|
From the SQL Server Management Studio, under the Tools menu, you will find "SQL Server Profiler".
Use this tool to see exactly what SQL statements are being run, then take those statements and run them in the SQL Server Manager, and under the Query menu click on the "Include Actual Execution Plan".
After running the query it will show you whether or not an index is used, which tables (or indexes) are being scanned, etc.
Cool stuff.
Good luck.
|
|
|
|
|
Thanks a bunch, that looks like exactly the thing I was looking for. The reason I couldn't find it was because, on my machine, I have a SQL express version of the Management Studio from which the Profiler seems to be missing. When I work directly on the machine that runs SQL Server, it is there.
Now I need to figure out how to set up a trace and how to make it meaningful. But thanks, I've got something to work from now.
|
|
|
|
|
|
Hi everybody. I have a problem. I am trying to make a little social network. Users can add other users to own people list. So what should i do ? Should i create a new table for every user? ( for example 2000 table for 2000 users ) or should i create an array for every user ? After i can save the array to the database. or any other way ? whick way is the best ? İf array is the best , how can i do it ? first user have 2 friends , second user have 19 friend. array size is different.
So what should i do ?
|
|
|
|
|
This is a classic case of a Many to Many relationship and what you need to do is create a linking table. You have the original Users table with each user having a unique id UserID. Then you have another table call it Friends with only 2 fields UserID and FriendID. Each time a user adds a friend, you just add a row to the linking table, using the UserID of the Friend as the FriendID in the friends table, and if they remove the friend, you just drop the row.
Hope this makes sense.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
this could work ! Thank you !
|
|
|
|
|
Glad to help
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
chuckcan08 wrote: this could will work
FTFY 
|
|
|
|
|
Hello Experts,
I have 1 Lacs Records in Excel or XML. And i want to insert those records in Live Database in single Event.
So pls help me for this issue.
If you can think then I Can.
|
|
|
|
|
My advice would be to convert the excel/xml file to a csv file and then you can use LOAD DATA INFILE[^] syntax to upload the data. This will be much quicker than using a transaction and Insert statements, especially if you are creating indexes. See Speed of Insert Statements[^] to get an idea of what is involved.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
You know "Lacs" is not an English word... Always remember to say 100K or 100,000.
|
|
|
|
|
SilimSayo wrote: You know "Lacs" is not an English word
It is indeed an English word (although the spelling is not correct).
You meant it is not American, right?
|
|
|
|
|
|
Hi All,
Iam new to database. Here iam using sql server 2000. i want to sum the counts from a table. I need this to calculate the employees who have come late in a week/month.
Example: I have table such as
Table1:tbl_attandence(it contains the employee id and the intimings and outtime)
Fields:empcode,intime,outime
Eg: EMP001,11/1/2011 09:30:45 AM,11/30/2011 06:30:15 PM
I am attaching my code such that it return the count but iam unable to sum the values because it is showing the result as shown below
DECLARE @actualTime DATETIME
DECLARE @lateTime DATETIME
SELECT @actualTime = '9:41'
Select @lateTime ='10:00'
select count(convert(varchar,intime,105)) as dates,CONVERT(VARCHAR(5),intime,108) AS Hours from tbl_attendance
where empcode='EMP001';
and intime between '2011-11-01' and '2011-11-30'
and CONVERT(VARCHAR(5),intime,108) >=@actualTime and CONVERT(VARCHAR(5),intime,108) <= @lateTime
group by CONVERT(VARCHAR(5),intime,108)
OytPut:
days intime
1 09:41
1 09:44
1 09:46
2 09:53
1 09:54
Now i need to sum all the days and get the sum as 7
Please suggest that how can i do this task any help will be appreciated
|
|
|
|
|
Elizabeth Rani wrote: Now i need to sum all the days and get the sum as 7
Seems to add up to 6!
You can used compute after you query in the proc
compute sum(Days)
or put the existing query into another outer query
Select sum(days) from (put your query here)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everyone! I'm currently learning Database Administration and trying to play with database backup and restore. I have created a database that I am using to try what I've learnt so far.
I'm trying to restore a backup database but I always get an error that '... Restore failed on server ...' when I still have the database on the server. But if I delete the database from the server and perform the restore, everything works fine.
However, when I create a backup of 'AdventureWorks' database and perform the restore, I do not encounter any problem whether 'AdventureWorks' database still exists on the server or not. I'm thinking the problem be occuring from some database properties I do not know. I need help on how to solve this problem of restoring the backup when the database still exists on the server as it works with 'AdventureWorks'. Thanks in advance.
|
|
|
|
|
Hi,
When you are doing a SQL database restore, are you over riding the current database?
1.In the restore window, select the database source for backup.
2.select options from the left pane.
3.check 'over write the existing database' check box.
4.Restore.
Hope this helps.
-Manognya
__________________________________________________
$ God gives what is best.Not what all you wish
|
|
|
|
|
Does the service have read/write privileges on the mdf-file and read-privileges the location where your backup is located?
Bastard Programmer from Hell 
|
|
|
|
|
hello guys... I have this numeric column whose '<b>Identity Specification</b>' I have set to <b>Yes </b>. But sometimes problem occurs when I use this technique due to the fact that, when I delete all the records from this table, this column's ID does not reset to 1. Instead, it retains the last ID (the ID before deleting all the records).
SO the question is: how can I reset this column to 1, after I delete all the records from this table? thnx for any help.
|
|
|
|
|
You need to Reseed the Column using the DBCC command of MS SQL Server.
dbcc CHECKIDENT("TableName1",RESEED,0)
DBCC CHECKIDENT for Microsoft SQL Server
I hope this help with your Problem
_____________________________________________________________________
Hey guys. Just to defend my answer from the birds and stones .
The Reason for me using the Delete/DBCC CHECKIDENT Function is that you can still use Auditing on the tables if needed
(The Triggers are still called). With a truncate table call the triggers will not be called Truncate Table
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
Cheers
modified 10-Jan-12 0:39am.
|
|
|
|
|
Thnx for your answer. It perfectly solves my problem
|
|
|
|
|
If there are no foreign keys on the table I guess you can always use:
truncate table <table_name>
to kill 2 birds with one stone...
|
|
|
|
|
Don't let the animal welfare people hear you talk of "killing two birds with one stone". 
|
|
|
|
|
Yes, not very PC of me, obviously if I wanted two birds killed I wouldn't use something as inelegant as a stone, I'd simply put a contract out on them... 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
