Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advance
Kevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com
|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance
|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest
.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Finding all "DEFAULT" constraints on a table using Information_Sch

I have a couple stored procedures in which I am trying to determine all of
the "Default" constraints that are on a given table. I know that this
information is available in the sysobjects table, but I'm trying to avoid
directly querying the system tables whenever possible in favor of using the
Information Schema views. My problem is that the Table_Constraints view
appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constraints
.
Is there any other Information Schema view that I can use to see the DEFAULT
constraints?information_schema.columns. Will have the default values.
Hope this helps.|||Try using system table sysconstraints.
Example:
use northwind
go
select
object_name([id]) as table_name,
object_name(constid) as constraint_name,
col_name([id], colid) as column_name
from
sysconstraints
where
[id] = object_id('dbo.orders')
and objectproperty(constid, 'IsDefaultCnst') = 1
go
AMB
"DoubleBlackDiamond" wrote:

> I have a couple stored procedures in which I am trying to determine all of
> the "Default" constraints that are on a given table. I know that this
> information is available in the sysobjects table, but I'm trying to avoid
> directly querying the system tables whenever possible in favor of using th
e
> Information Schema views. My problem is that the Table_Constraints view
> appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constrain
ts.
> Is there any other Information Schema view that I can use to see the DEFA
ULT
> constraints?
>sql

Finding a tape drive

I am writing an interface for the SQL backup in my application. I am
currently trying to determine if a tape drive exists on the system so the
system can add it as a backup device. If I use enterprise manager to add
the tape drive as a device my application can see the device but still can't
see the tape drive directly. My system can see the hard drives and DVD
drive on my system. I have tried sp_helpdevices, xp_availablemedia, and
sysdevices.
Should I be looking somewhere else or does the user have to add the tape
from Enterprise manager first?
Regards,
JohnDepending on what interface you're developing, you can use SQL-DMO in
various languages to iterate through devices on your SQL Server and
tapes as well.
Here's a few helpful links that I found useful when I developed a SQL
Db management interface for the web.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
http://sqldev.net/sqldmo.htm|||Thanks for the replay.
I have been implementing this via the SqlClient in C# using the basic
"BACKUP <db> TO <device>" interface. I obtained a list of devices from the
sys_devices or xp_availablemedia and xp_subdir which all seems to work fine
except I don't see the tape device unless it's added as a device.
I have never worked with SQL-DMO and the example seems to have problem
compiling which I assume is because it was written for an older version of
C# / .NET. What is the advantage of using the SQL-DMO as opposed to just
using the SqlClient built into C#?
Regards,
John
"sze" <szeying.tan@.gmail.com> wrote in message
news:1122596876.912374.115650@.z14g2000cwz.googlegroups.com...
> Depending on what interface you're developing, you can use SQL-DMO in
> various languages to iterate through devices on your SQL Server and
> tapes as well.
> Here's a few helpful links that I found useful when I developed a SQL
> Db management interface for the web.
> http://msdn.microsoft.com/library/d... />
b_3tlx.asp
> http://sqldev.net/sqldmo.htm
>|||you may have problems compiling if you did not reference sqldmo.dll in
your .NET project.
it should be somewhere in your sql server installation, something like
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ ...
as for advantages, i don't really know. SQL-DMO is an API for SQL
Server administration. SQLClient is a data provider for C#... i'm
usually partial to using an API when i want to interface with a
particular application... since the API encapsulates all objects
available in the particular application.. and in this case, all objects
available in SQL Server (this means everything you can do in Enterprise
Manager). but of course, it's really your call.|||Thanks again, the problem was the strong naming I already knew I needed to
link into it. I found a link for the strong naming problem on the microsoft
site so it works now.
Regards,
John|||EXECUTE master.dbo.xp_get_tape_devices seems to do the trick.
Regards,
Johnsql

find value based on max(date)

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?select t.employee
, t.Deduction_type
, t.Date_entered
, t.Amount
from Sample as t
inner
join (
select employee
, Deduction_type
, max(Date_entered) as max_date
from Sample
group
by employee
, Deduction_type
) as m
on m.employee = t.employee
and m.Deduction_type = t.Deduction_type
and m.max_date = t.Date_enteredsql

find unique

hey all,
i have a table which i would like to determine what fields make each record
unique? Unfortunately there is not a tableid.
thanks,
rodchar>> i have a table which i would like to determine what fields [sic] make each record [sic]
unique? Unfortunately there is not a tableid [sic]. <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. This is a pain to do after the
fact.
1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
"query to a working table. This kindof crappy non-table probably has
accumulated garbage.
2) Pick the columns that should be keys from the data model. The data
model which you probably do not have.
3) Test this subset of columns with
SELECT 'Bad Choice'
FROM Foobar
GROUP BY <<list of columns>>
HAVING COUNT(*) > 1;
4) reduce the number of test columns when you get a winner.|||what's the [sic] mean?
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. This is a pain to do after the
> fact.
> 1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
> "query to a working table. This kindof crappy non-table probably has
> accumulated garbage.
> 2) Pick the columns that should be keys from the data model. The data
> model which you probably do not have.
> 3) Test this subset of columns with
> SELECT 'Bad Choice'
> FROM Foobar
> GROUP BY <<list of columns>>
> HAVING COUNT(*) > 1;
> 4) reduce the number of test columns when you get a winner.
>|||rodchar wrote:
> what's the [sic] mean?
http://www.freesearch.co.uk/dictionary/sic
David Gugick
Quest Software
www.imceda.com
www.quest.com

find datetime fields - swap year portion of datetime

I need to devise a t-sql script to:
1.) loop through all user tables in my db,
2.) determine whether each table has one or more datetime fields, and
if it does,
3.) swap the year portion of each rows datetime with a different year
(e.g., '1-Jan-1996' would become '1-Jan-2018')
I know how to do '1', have an idea how to do '3' using the DatePart
and CAST functions, but haven't had much luck figuring out '2', and
*then iterating row by row through the table's datetime rows to swap
in the new years. To further automate this, I envisioned adding a
table to the db that defined the mapping for years, i.e.,...
tblBlindedYears
YearNow |BecomesYear
1996 2018
1997 2008
(With this, I would look to eliminate leap year issues by making sure
I don't swap a non-leap year with a leap-year).
Any help GREATLY appreciated.To help you with the issue 2):
USE Northwind
GO
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE DATA_TYPE IN ('datetime','smaldatetime')
AND t.TABLE_TYPE='BASE TABLE'
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401021712.247c36da@.posting.google.com...
> I need to devise a t-sql script to:
> 1.) loop through all user tables in my db,
> 2.) determine whether each table has one or more datetime fields, and
> if it does,
> 3.) swap the year portion of each rows datetime with a different year
> (e.g., '1-Jan-1996' would become '1-Jan-2018')
> I know how to do '1', have an idea how to do '3' using the DatePart
> and CAST functions, but haven't had much luck figuring out '2', and
> *then iterating row by row through the table's datetime rows to swap
> in the new years. To further automate this, I envisioned adding a
> table to the db that defined the mapping for years, i.e.,...
> tblBlindedYears
> YearNow |BecomesYear
> 1996 2018
> 1997 2008
> (With this, I would look to eliminate leap year issues by making sure
> I don't swap a non-leap year with a leap-year).
> Any help GREATLY appreciated.|||This is very helpful, thank-you.
Now that I have this list of table names having counts of datetime
columns, any suggestions as to discovering *which* columns have the
datetime, and how I can begin printing these particular columns
values?
p.s. my results were:
dbo tbl10DayMeanAirTemp datetime 1
dbo tblHistoricalStationFlows datetime 1
dbo tblHistoricalStationWaterElevation datetime 1
dbo tblHistoricalStationWaterTemperatures datetime 1
dbo tblHydrologyModelWeeks datetime 2
dbo tblModelRuns datetime 5
dbo tblModelScenarios datetime 2
dbo tblXLFileLocations datetime 1
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<Ok3w2rd0DHA.1532@.TK2MSFTNGP10.phx.gbl>...
> To help you with the issue 2):
> USE Northwind
> GO
> SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
> FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
> WHERE DATA_TYPE IN ('datetime','smaldatetime')
> AND t.TABLE_TYPE='BASE TABLE'
> GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
> news:ed564348.0401021712.247c36da@.posting.google.com...
> > I need to devise a t-sql script to:
> >
> > 1.) loop through all user tables in my db,
> > 2.) determine whether each table has one or more datetime fields, and
> > if it does,
> > 3.) swap the year portion of each rows datetime with a different year
> > (e.g., '1-Jan-1996' would become '1-Jan-2018')
> >
> > I know how to do '1', have an idea how to do '3' using the DatePart
> > and CAST functions, but haven't had much luck figuring out '2', and
> > *then iterating row by row through the table's datetime rows to swap
> > in the new years. To further automate this, I envisioned adding a
> > table to the db that defined the mapping for years, i.e.,...
> >
> > tblBlindedYears
> > YearNow |BecomesYear
> > 1996 2018
> > 1997 2008
> >
> > (With this, I would look to eliminate leap year issues by making sure
> > I don't swap a non-leap year with a leap-year).
> >
> > Any help GREATLY appreciated.|||JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> This is very helpful, thank-you.
> Now that I have this list of table names having counts of datetime
> columns, any suggestions as to discovering *which* columns have the
> datetime, and how I can begin printing these particular columns
> values?
>
Here is a query that returns the column names as well:
select o.name, c.name
from sysobjects o
join syscolumns c on o.id = c.id
join systypes t on c.type = t.type
where t.name in ('datetime', 'smalldatetime')
order by o.name, c.name
(I assuming that everything is owned by dbo.)
To run some general code that goes through all columns, you would probably
have to use dynamic SQL to build statements on the fly.
If you are not acquainted with dynamic SQL, I have an article on my
web site, http://www.sommarskog.se/dynamic_sql.html. The article may
seem dauntingly long, but the article is trying to tell you that
you should use this feature with care. But this seems to be a good
occasion for using dynamic SQL.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9466475B2A41Yazorman@.127.0.0.1>...
> JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> > This is very helpful, thank-you.
> >
> > Now that I have this list of table names having counts of datetime
> > columns, any suggestions as to discovering *which* columns have the
> > datetime, and how I can begin printing these particular columns
> > values?
> >
> Here is a query that returns the column names as well:
> select o.name, c.name
> from sysobjects o
> join syscolumns c on o.id = c.id
> join systypes t on c.type = t.type
> where t.name in ('datetime', 'smalldatetime')
> order by o.name, c.name
> (I assuming that everything is owned by dbo.)
> To run some general code that goes through all columns, you would probably
> have to use dynamic SQL to build statements on the fly.
> If you are not acquainted with dynamic SQL, I have an article on my
> web site, http://www.sommarskog.se/dynamic_sql.html. The article may
> seem dauntingly long, but the article is trying to tell you that
> you should use this feature with care. But this seems to be a good
> occasion for using dynamic SQL.
Thank-you very much. This is helpful, and I plan to read the dynamic SQL article.

Find Counties in area

We have the need to determine surrounding counties of a particular county.

For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa

Could it be determined by the longitude and latitude of the zip code in comparison with the counties?

Hello,

Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)

tCounty (CountyID int, CountyName varchar)

tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)

So if Contra Costa was near Alameda, tCountry might contain rows:

1, Contra Costa

2, Alameda

And tCountyAdjoined would contain:

1, 2

Obviously, a simple join then returns all counties that adjoin a particular County.

Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)

Cheers,

Rob