Showing posts with label analyze. Show all posts
Showing posts with label analyze. Show all posts

Sunday, February 19, 2012

Analyzing sysjobhistory

Has anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.

I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).

I would be grateful for any input/insight.

Regards,

hmscottIts advised not to query system tables directly when system supplied stored procedures are availble, have you looked into SP_HELP_JOBHISTRY and other corresponding SPs. Refer to books online for more information.|||Satya,

Yes, I had looked at that SP. The basic issue I had with it was that the Date and time stamps are configured as INTs and are not combined into an integrated date/time field.

Your reminder is a good one, however; I am nearly finished with working on a centralized repository for job execution history. I will look again and try to avoid querying the system tables directly.

Regards,

hmscott

ANALYZING SQL SERVICE HANGUP

The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>

Analyzing db Growth per day

Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.

Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.

Analyze SqlServer 7.0 trace output

In one of our SqlServer 7.0 databases we get the following
error message:
Database AllWork: Transaction Log Backup...
Destination:
[e:\SQL7Data\BACKUP\AllWork\AllWork_tlog_200403290 853.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
allow BACKUP LOG because file 'AllWork' has been subjected
to nonlogged updates and cannot be rolled forward. Perform
a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or
restore operation terminating abnormally.
After a full database backup, everything is fine for some
time, but then the problem reoccurs.
I managed to capture a full trace using Profiler. At 7:40
the transaction log backup succeeded without errors and at
7:45 the transaction log backup failed with the
errormessage shown above. Allthough the trace is only 5
minutes, it still is over 1.100.000 records. I already
looked for SELECT INTO, WRITETEXT and UPDATETEXT
statements but I can't find any.
How can I determine what causes this error message?
What should I look for in the trace?
Any help would be appreciated,
TIA,
Gerrit van Ham
Gerrit,
Is 'select into/bulk copy' switched on? If so, there may be a bcp fast load taking place. I can't remember if this gets shown in the profiler trace in SQL7 or not - I don't think it does, but you may need to check this. It sounds like a bcp operation is t
aking place with select into/bulk copy switched on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Analyze SPS

What is the program microsoft has developed to help optimize Stored
procedures? (I am not talking about the quesry analyzer).
SeanSean,
You might not be talking about Query Analyzer, but that is what we
have and what we use. In particular, the graphical query plan feature
of Query Analyzer is indispensible.
There is also the Index Tuning Wizard. Search Books on Line for
details. Tuning a query, or a proc, is often a case of indexing
properly.
Roy
On Fri, 28 Apr 2006 13:07:53 -0700, <Seanms@.keeyon.com> wrote:

>What is the program microsoft has developed to help optimize Stored
>procedures? (I am not talking about the quesry analyzer).
>Sean|||Seanms@.keeyon.com wrote:
> What is the program microsoft has developed to help optimize Stored
> procedures? (I am not talking about the quesry analyzer).
> Sean
Profiler for example. It will help you track down performance-critical
pieces of code. Could you explain what sort of functionality you are
looking for that isn't otherwise in QA?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Analyze LDF files MS SQL Server 2000

Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
I mean, a tool that converts a LDF file in a set of SQL transactions?
(similar to dbtran in sybase)

thanks!"Esteban" <ecastillo@.gmail.com> wrote in message
news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> I mean, a tool that converts a LDF file in a set of SQL transactions?
> (similar to dbtran in sybase)
> thanks!

There is nothing in MSSQL itself (except for some undocumented commands),
but there are third-party tools available, such as this one:

http://www.lumigent.com/

If you are trying to achieve something specific, you might want to post more
details of your goal, and someone may be able to suggest something.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40d3fef1_2@.news.bluewin.ch>...
> "Esteban" <ecastillo@.gmail.com> wrote in message
> news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> > Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> > I mean, a tool that converts a LDF file in a set of SQL transactions?
> > (similar to dbtran in sybase)
> > thanks!
> There is nothing in MSSQL itself (except for some undocumented commands),
> but there are third-party tools available, such as this one:
> http://www.lumigent.com/
> If you are trying to achieve something specific, you might want to post more
> details of your goal, and someone may be able to suggest something.
> Simon

Thanks Simon, I was trying this product, but I can't find an option
that shows me a list of SQL transactions made in the database ...
maybe I don't know how to use it.
For example, I want to know if a certain register was deleted from the
database.

thanks.|||Esteban (ecastillo@.gmail.com) writes:
> Thanks Simon, I was trying this product, but I can't find an option
> that shows me a list of SQL transactions made in the database ...
> maybe I don't know how to use it.
> For example, I want to know if a certain register was deleted from the
> database.

In the version I have, there is a "View DDL commands" under Browse, and it
seems to have the information of the kind you are looking for. There is
also a filter function.

Note that for Log Explorer to be really useful, you need to have the
database in FULL or BULK_LOGGED recovery mode.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Analyze Key Influencers Questions

I will be grateful if you could answer a few more questions around Analyzing Key Influencers

1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a ‘Score’ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?

2. Can I access this ‘Score’ and recommended inputs above programmatically?

3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?

5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the ‘Score’? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?

6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?

Thanks bunches

MA

Please see inlines:

1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a ‘Score’ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?

Entropy based, on top of a small data sample.

2. Can I access this ‘Score’ and recommended inputs above programmatically?

No

3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?

Entropy based and they cannot be invoked programatically. One could write a plug-in algorithm with the sole purpose of performing advanced feature selection/extraction operation. However, such an algorithm is not currently included in Analysis Services.

5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the ‘Score’? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?

The score only signifies a relative importance among all other factors. Here is briefly how that viewer works:

- it generates a fake input, containing, for each input attribute state, one row, with that input attribute state and everything else on NULL (Missing)

- it runs one prediction, fetches the histogram and compares the probabilities for 0 vs 1

- it normalizes the probabilities to scores, between 0 and 100

So, assuming your model has IncomeLevel as the only input attribute, the viewer practically executes a prediction for each possible state of the IncomeLevel attribute.

If the input is continuous, then is executes a prediction for each quartile of the input attribute

Now, with this info, LR could be used for feature selection if the target is binary. However, there are a few potential issues:

- LR is implemented as Neural Networks without hidden layers. On large volumes of data, training may be slow.

- LR has a feature selection parameter itself (default is top 255). That feature selection is entropy based and it means that the LR part will only see the remaining 255 features (so be sure to adjust the value of that parameter)

- for small data sets, the coefficients detected by the Neural net for some of the features are not really meaningful (as the network training goal is reached fast, by adjusting only a few coefficients) so the relative importance may not be reliable in the lower part of the viewer (results with small scores)

If you decide to use LR, you can call directly the stored procedure used by the viewer. Furthermore, the stored proc signature allows you to pinpoint some of the inputs and sort only the others

6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?

Continuous inputs can be discretized to be used in Naive Bayes. Naive Bayes has a few advantages:

- it is pretty straightforward and the results are easy to interpret

- it trains very fast.

- it works with any kind of predictable targets (binary or not, discrete or continuous - via discretization)

|||

Thank you very much Bogdan!!

MA

Analyze CPU Time per SQL Session

You have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?
Roy Harvey
Beacon Falls, CT
On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
wrote:

>Hello
>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>possible to analyze which SQL Server Session is consuming how much Processor
>Ressource in Time or Percent? With the DMV Views i see that there are 11000
>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>Taskmanager). This server is dedicated for SQL Server.
>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>must now, how many CPU Consumption per Session, so i can identify the
>Sessions who are responsable for the high CPU Workload
>Somebody knows how i can use the DMV Views or are there tools to retrieve
>the information?
>regards
>Paolo
>
It's not an ideal approach, but you could always take snapshots of the DMV
and find the cpu time differences between snapshots, and that should give you
some rough idea.
Linchi
"Paolo Taverna" wrote:

> Hi Roy
> yes i had a look at the column cpu_time. So far i understand is the column
> cpu_time the sum of the total cpu_time of the request since this Session is
> alive. So if the column last_request_end_time is a lot behind the time when
> the CPU's have high workload, the column total_cpu_time is not helpful.
> Regards Paolo
> "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
> news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
>
>

Analyze CPU Time per SQL Session

Hello
Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
possible to analyze which SQL Server Session is consuming how much Processor
Ressource in Time or Percent? With the DMV Views i see that there are 11000
open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
Taskmanager). This server is dedicated for SQL Server.
With the View sys.dm_exec_sessions i see how many Sessions are open, but i
must now, how many CPU Consumption per Session, so i can identify the
Sessions who are responsable for the high CPU Workload
Somebody knows how i can use the DMV Views or are there tools to retrieve
the information?
regards
PaoloYou have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?
Roy Harvey
Beacon Falls, CT
On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
wrote:
>Hello
>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>possible to analyze which SQL Server Session is consuming how much Processor
>Ressource in Time or Percent? With the DMV Views i see that there are 11000
>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>Taskmanager). This server is dedicated for SQL Server.
>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>must now, how many CPU Consumption per Session, so i can identify the
>Sessions who are responsable for the high CPU Workload
>Somebody knows how i can use the DMV Views or are there tools to retrieve
>the information?
>regards
>Paolo
>|||Hi Roy
yes i had a look at the column cpu_time. So far i understand is the column
cpu_time the sum of the total cpu_time of the request since this Session is
alive. So if the column last_request_end_time is a lot behind the time when
the CPU's have high workload, the column total_cpu_time is not helpful.
Regards Paolo
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> You have already found sys.dm_exec_sessions. Have you looked at the
> column cpu_time it returns?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> wrote:
>>Hello
>>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>>possible to analyze which SQL Server Session is consuming how much
>>Processor
>>Ressource in Time or Percent? With the DMV Views i see that there are
>>11000
>>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>>Taskmanager). This server is dedicated for SQL Server.
>>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>>must now, how many CPU Consumption per Session, so i can identify the
>>Sessions who are responsable for the high CPU Workload
>>Somebody knows how i can use the DMV Views or are there tools to retrieve
>>the information?
>>regards
>>Paolo|||It's not an ideal approach, but you could always take snapshots of the DMV
and find the cpu time differences between snapshots, and that should give you
some rough idea.
Linchi
"Paolo Taverna" wrote:
> Hi Roy
> yes i had a look at the column cpu_time. So far i understand is the column
> cpu_time the sum of the total cpu_time of the request since this Session is
> alive. So if the column last_request_end_time is a lot behind the time when
> the CPU's have high workload, the column total_cpu_time is not helpful.
> Regards Paolo
> "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
> news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> > You have already found sys.dm_exec_sessions. Have you looked at the
> > column cpu_time it returns?
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> > wrote:
> >
> >>Hello
> >>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
> >>possible to analyze which SQL Server Session is consuming how much
> >>Processor
> >>Ressource in Time or Percent? With the DMV Views i see that there are
> >>11000
> >>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
> >>Taskmanager). This server is dedicated for SQL Server.
> >>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
> >>must now, how many CPU Consumption per Session, so i can identify the
> >>Sessions who are responsable for the high CPU Workload
> >>
> >>Somebody knows how i can use the DMV Views or are there tools to retrieve
> >>the information?
> >>
> >>regards
> >>Paolo
> >>
>
>

Analyze Backup Server

I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit networkdaveg.01@.gmail.com wrote:
> I am trying to analyze/identify the bottleneck on our new backup
> server. I was wondering if someone could recommend some performance
> counters to watch.
> Right now I am watching "% Write Time" I am averaging around
> "500" for this measure while copying a 50GB database file. It is
> taking almost twice as long to copy the file to a different server as
> opposed to a different direct attached array. The % disk write counter
> is similar on both servers. Am I wrong to assume that the physical
> disks are the bottleneck (due to high performance counter ratings)?
>
> Does anyone know some general guide lines on real world throughput on
> the following? What kinds of things should I be looking at and/or
> asking our IT department?
> Gigabit dedicated network
> Perc4 (I read on Dells site that it is 320MB/s which translates to
> 1.1TB / hour)
> Raid 5 using 6 - 300MB disks (10,000 RPM)
> Other bottleneck candidate?
>
> Server Configuration
> Windows Server 2003
> Dell PowerEdge 2850
> Perc4 - Raid 5
> PowerVault 200s
> Gigabit network
>
5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
the disk level. You might verify that the disk transfer rate
is full Ultra320. A cabling or termination problem might
reduce the transfer rate.
The PERC4 listed here, has a 64 bit 66MHz interface, and as long
as it is fully utilizing the bus, the bus should not be a limit.
Sometimes a bus segment can be slowed, by the presence of a slower
card. So you may want to check the card configuration and bus
structure of your server.
http://www.jjwei.com/shop/item.asp?itemid=78
But your Gigabit Ethernet only does 125MB/sec theoretical in one
direction, so a single transaction with your server will be limited
by the network. A local transfer on the server itself could go faster.
Plenty of little things to check.
Paul|||Assuming everything is configured properly, how long would I expect it
to take to transfer a 50GB file to the backup server?
If the perfmon coutner "%disk write" goes above 100 should I assume
that the physical disks are the bottleneck?
Paul wrote:
> daveg.01@.gmail.com wrote:
> > I am trying to analyze/identify the bottleneck on our new backup
> > server. I was wondering if someone could recommend some performance
> > counters to watch.
> >
> > Right now I am watching "% Write Time" I am averaging around
> > "500" for this measure while copying a 50GB database file. It is
> > taking almost twice as long to copy the file to a different server as
> > opposed to a different direct attached array. The % disk write counter
> > is similar on both servers. Am I wrong to assume that the physical
> > disks are the bottleneck (due to high performance counter ratings)?
> >
> >
> > Does anyone know some general guide lines on real world throughput on
> > the following? What kinds of things should I be looking at and/or
> > asking our IT department?
> >
> > Gigabit dedicated network
> > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > 1.1TB / hour)
> > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > Other bottleneck candidate?
> >
> >
> > Server Configuration
> > Windows Server 2003
> > Dell PowerEdge 2850
> > Perc4 - Raid 5
> > PowerVault 200s
> > Gigabit network
> >
> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> the disk level. You might verify that the disk transfer rate
> is full Ultra320. A cabling or termination problem might
> reduce the transfer rate.
> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> as it is fully utilizing the bus, the bus should not be a limit.
> Sometimes a bus segment can be slowed, by the presence of a slower
> card. So you may want to check the card configuration and bus
> structure of your server.
> http://www.jjwei.com/shop/item.asp?itemid=78
> But your Gigabit Ethernet only does 125MB/sec theoretical in one
> direction, so a single transaction with your server will be limited
> by the network. A local transfer on the server itself could go faster.
> Plenty of little things to check.
> Paul|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
>
> Paul wrote:
> > daveg.01@.gmail.com wrote:
> > > I am trying to analyze/identify the bottleneck on our new backup
> > > server. I was wondering if someone could recommend some performance
> > > counters to watch.
> > >
> > > Right now I am watching "% Write Time" I am averaging around
> > > "500" for this measure while copying a 50GB database file. It is
> > > taking almost twice as long to copy the file to a different server as
> > > opposed to a different direct attached array. The % disk write counter
> > > is similar on both servers. Am I wrong to assume that the physical
> > > disks are the bottleneck (due to high performance counter ratings)?
> > >
> > >
> > > Does anyone know some general guide lines on real world throughput on
> > > the following? What kinds of things should I be looking at and/or
> > > asking our IT department?
> > >
> > > Gigabit dedicated network
> > > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > > 1.1TB / hour)
> > > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > > Other bottleneck candidate?
> > >
> > >
> > > Server Configuration
> > > Windows Server 2003
> > > Dell PowerEdge 2850
> > > Perc4 - Raid 5
> > > PowerVault 200s
> > > Gigabit network
> > >
> >
> > 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> > the disk level. You might verify that the disk transfer rate
> > is full Ultra320. A cabling or termination problem might
> > reduce the transfer rate.
> >
> > The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> > as it is fully utilizing the bus, the bus should not be a limit.
> > Sometimes a bus segment can be slowed, by the presence of a slower
> > card. So you may want to check the card configuration and bus
> > structure of your server.
> >
> > http://www.jjwei.com/shop/item.asp?itemid=78
> >
> > But your Gigabit Ethernet only does 125MB/sec theoretical in one
> > direction, so a single transaction with your server will be limited
> > by the network. A local transfer on the server itself could go faster.
> > Plenty of little things to check.
> >
> > Paul|||I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit network|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
If the transfer is over the network, the limit is 125MB/sec. To transfer
a 50GB file takes 400 seconds.
Paul
> Paul wrote:
>> daveg.01@.gmail.com wrote:
>> I am trying to analyze/identify the bottleneck on our new backup
>> server. I was wondering if someone could recommend some performance
>> counters to watch.
>> Right now I am watching "% Write Time" I am averaging around
>> "500" for this measure while copying a 50GB database file. It is
>> taking almost twice as long to copy the file to a different server as
>> opposed to a different direct attached array. The % disk write counter
>> is similar on both servers. Am I wrong to assume that the physical
>> disks are the bottleneck (due to high performance counter ratings)?
>>
>> Does anyone know some general guide lines on real world throughput on
>> the following? What kinds of things should I be looking at and/or
>> asking our IT department?
>> Gigabit dedicated network
>> Perc4 (I read on Dells site that it is 320MB/s which translates to
>> 1.1TB / hour)
>> Raid 5 using 6 - 300MB disks (10,000 RPM)
>> Other bottleneck candidate?
>>
>> Server Configuration
>> Windows Server 2003
>> Dell PowerEdge 2850
>> Perc4 - Raid 5
>> PowerVault 200s
>> Gigabit network
>> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
>> the disk level. You might verify that the disk transfer rate
>> is full Ultra320. A cabling or termination problem might
>> reduce the transfer rate.
>> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
>> as it is fully utilizing the bus, the bus should not be a limit.
>> Sometimes a bus segment can be slowed, by the presence of a slower
>> card. So you may want to check the card configuration and bus
>> structure of your server.
>> http://www.jjwei.com/shop/item.asp?itemid=78
>> But your Gigabit Ethernet only does 125MB/sec theoretical in one
>> direction, so a single transaction with your server will be limited
>> by the network. A local transfer on the server itself could go faster.
>> Plenty of little things to check.
>> Paul
>|||On Sun, 14 Jan 2007 06:54:21 -0500, Paul <nospam@.needed.com>
wrote:
>Dave wrote:
>> Assuming everything is configured properly, how long would I expect it
>> to take to transfer a 50GB file to the backup server?
>> If the perfmon coutner "%disk write" goes above 100 should I assume
>> that the physical disks are the bottleneck?
>If the transfer is over the network, the limit is 125MB/sec. To transfer
>a 50GB file takes 400 seconds.
> Paul
>
I'd be surprised if a real transfer averages over 90MB/s,
especially if TCP/IP

Analyze 2 Columns in 1 Table

we have about 50 columns in 1 table but only want to analyze 2 of them

column 1 = Source
source is dynamic as far as the contents are concern
source
======
h1
h2
t1
t2
DM
PS

column 2 = trans
trans can only have 5 different values as the last value
trans
======
R
RRR
RRRRR
N
U
X
E
NRR

so i would analyze the right(trans,1) = 'whatever'

I need a query - hopefully 1 liner - that can analyze the count of source by trans so the outcome would look something lke this with totals if possble in the string , if not fine

t_src N R X U E total
h1 0 31 1 0 14 46
h2 0 43 2 0 12 57
t1 0 33 4 0 8 45
t2 0 11 3 0 9 23
dm 64 12 0 6 0 82
ps 32 4 1 5 0 42
total 96 134 11 11 43 295

where the left(trans,1) becomes the column name header and the t_src becomes the row header

thnxI'd use:SELECT t_src
, Sum(CASE WHEN 'N' = Left(trans, 1) THEN 1 END) AS N
, Sum(CASE WHEN 'R' = Left(trans, 1) THEN 1 END) AS R
, Sum(CASE WHEN 'X' = Left(trans, 1) THEN 1 END) AS X
, Sum(CASE WHEN 'U' = Left(trans, 1) THEN 1 END) AS U
, Sum(CASE WHEN 'E' = Left(trans, 1) THEN 1 END) AS E
, Count(*) AS 'total'
FROM theTable
GROUP BY t_src-PatP|||thanx
i didn't realize it was that simple.
Is there any way to get the totals at the bottom with the query you gave me

or should i just use another query(which is fine by me)|||There are ways, but they aren't really pretty. I'd just use the same query and a UNION ALL of that query using a constant like 'All' for the src_id but without the GROUP BY to get the footer totals.

-PatP|||i got this - total works , but the total is on top -- how can I make it become a footer
SELECT '' as 'Total'
SELECT '' shows blank under the t_src column

SELECT t_src
, Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) AS 'New'
, Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
, Count(*) AS 'Total'
FROM theTABLE where t_src is not null group by t_Src
union
SELECT '' as 'Total'
, Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) as 'New'
, Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
, Count(*) AS 'Total'
FROM theTABLE where t_src is not null|||Ok, time to get out the "big hammer" and get fiesty!SELECT t_src
, Sum(CASE WHEN 'N' = foo THEN 1 END) AS 'New'
, Sum(CASE WHEN 'R' = foo THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'X' = foo THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'U' = foo THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'E' = foo THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN foo NOT IN ('N', 'R', 'X', 'U', 'E') THEN 1 END) AS Other
, Count(*) AS 'total'
FROM (
SELECT t_src, Right(hftr, 1) AS foo, 1 AS bar
FROM theTable
UNION ALL SELECT '', Right(hftr, 1), 2
FROM theTable) AS a
WHERE t_src IS NOT NULL
GROUP BY t_src, bar
ORDER BY bar-PatP|||i get it
the group by bar allows the total to become a footer
I understand the code
Thank you for all your help and time

Analyze / Data Mining Ribbons

I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.

When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?

I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?

I have been through "Getting Started" and set the AS connection to the local AS Server.

Thanks

Sutha

Possibly the Add-ins have been disabled due to an exception or other fault when opening them. You could try the following ...

Open Excel.

From the Office Button in the top left hand corner of the Excel UI, click the "Excel Options" button.

Navigate to the "Add-ins" page.

In the "Manage" drop down (at the bottom of the page) select "Disabled items".

Select the data mining addins and click "Enable".

Close the dialogs and close Excel

Restart Excel.

|||

Donald

I did check that out already. Nothing has been disabled.

I did notice something though. If I select "Com Add-Ins" from "Add-ins" page I get the following list.

1. Send To Bluetooth

2. SQLServer.DMClientXLAddIn

3. SQLServer.DMXLAddIn

4. TFC Office Shim

The "SQLServer.DMClientXLAddIn" and "SQLServer.DMXLAddIn" were not selected. If I select and click OK and re-start excel it removes it.

Thanks

Sutha

|||

This seems to be a corrupted installation. You could try uninstalling and re-installing the add-ins.

Are you running Vista? If this is the case, can you try the same steps as above (select Com Add-ins, then check the SQLServer add-ins) AFTER starting Excel as Administrator? (Create a shortcut to excel, right click, select run as Admin...)

|||I am having the same problem.

The DM Ribbon showed up when I was using the Office 2007 trial edition. After I had someone in my IT department install just Excel 2007 on my computer, I reinstalled the add ins. I receive the following error message when trying to install the com objects: "Not loaded. A run-time error occurred during the loading of the COM add in".

Brian
|||

Did you try the steps that I suggested to restore the add-ins? And were you running as administrator?

|||Yes and yes.
|||Then I do suspect the installation is corrupt. I would suggest using MS product support to work through this one. It may be difficult for us to discover over the forum whether this is an Excel or Add-in issue.|||Has anyone resolved this error? We're having the same problem. It can't load mscoree.dll for some reason. We have implemented the steps stated above but excel still can't load the add-in.|||

We're encountering the same problem here. In our case, we needed to install the Add-In to 9 PCs. Only 4 has no problems, the rest has the same common problem. All PCs are running the same specifications (WinXP,512MB).

We've tried checking the following:

1. Re-installed the Add-In

2. Installed SQL2K5 client tools

3. Re-installed the add-in using administrator priviledges

4. Tweaked the registry

5. Checked the configuration using excel options (like what Donald suggested)

Surprisingly, we've encountered a common error status message while trying to enable the DM Add-Ins. “Not loaded. A runtime error occurred during the loading of the COM Add-In”. We encounter this as a status message when trying to select the add-in from the manage dialog box.

Any insights on this one?

|||Sure, I had that error before contacting MS Support for the fix.

In my case, it was because the .NET Programmability Support for Excel 2007 was not installed. The Add-ins are written in .NET, so no support means no add-ins.

1. Under Add/Remove Programs, select Office 2007.
2. Select Change.
3. In the pop-up, select Add or Remove Features.
4. Under Microsoft Excel, check to see if .NET Programmability Support is Not Available, and change it to Run from My Computer. (I think this is not installed by default, not sure)

That fixed it for me. If not, check if .NET Framework 2.0 is installed as well. It really seems to me that it's a .NET problem.

Good luck!

-Tim

|||

Tim,

This is freaky. I discovered this about an hour before I received your response.

The .Net stuff is definitely not installed by default in the Excel 2007 typical installation.

Thanks for the tip this solved my problem for the data mining ribbon.

However, I still cannot get the table add-on package to install the table ribbon.

-Brian

Analyze / Data Mining Ribbons

I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.

When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?

I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?

I have been through "Getting Started" and set the AS connection to the local AS Server.

Thanks

Sutha

Possibly the Add-ins have been disabled due to an exception or other fault when opening them. You could try the following ...

Open Excel.

From the Office Button in the top left hand corner of the Excel UI, click the "Excel Options" button.

Navigate to the "Add-ins" page.

In the "Manage" drop down (at the bottom of the page) select "Disabled items".

Select the data mining addins and click "Enable".

Close the dialogs and close Excel

Restart Excel.

|||

Donald

I did check that out already. Nothing has been disabled.

I did notice something though. If I select "Com Add-Ins" from "Add-ins" page I get the following list.

1. Send To Bluetooth

2. SQLServer.DMClientXLAddIn

3. SQLServer.DMXLAddIn

4. TFC Office Shim

The "SQLServer.DMClientXLAddIn" and "SQLServer.DMXLAddIn" were not selected. If I select and click OK and re-start excel it removes it.

Thanks

Sutha

|||

This seems to be a corrupted installation. You could try uninstalling and re-installing the add-ins.

Are you running Vista? If this is the case, can you try the same steps as above (select Com Add-ins, then check the SQLServer add-ins) AFTER starting Excel as Administrator? (Create a shortcut to excel, right click, select run as Admin...)

|||I am having the same problem.

The DM Ribbon showed up when I was using the Office 2007 trial edition. After I had someone in my IT department install just Excel 2007 on my computer, I reinstalled the add ins. I receive the following error message when trying to install the com objects: "Not loaded. A run-time error occurred during the loading of the COM add in".

Brian
|||

Did you try the steps that I suggested to restore the add-ins? And were you running as administrator?

|||Yes and yes.
|||Then I do suspect the installation is corrupt. I would suggest using MS product support to work through this one. It may be difficult for us to discover over the forum whether this is an Excel or Add-in issue.|||Has anyone resolved this error? We're having the same problem. It can't load mscoree.dll for some reason. We have implemented the steps stated above but excel still can't load the add-in.|||

We're encountering the same problem here. In our case, we needed to install the Add-In to 9 PCs. Only 4 has no problems, the rest has the same common problem. All PCs are running the same specifications (WinXP,512MB).

We've tried checking the following:

1. Re-installed the Add-In

2. Installed SQL2K5 client tools

3. Re-installed the add-in using administrator priviledges

4. Tweaked the registry

5. Checked the configuration using excel options (like what Donald suggested)

Surprisingly, we've encountered a common error status message while trying to enable the DM Add-Ins. “Not loaded. A runtime error occurred during the loading of the COM Add-In”. We encounter this as a status message when trying to select the add-in from the manage dialog box.

Any insights on this one?

|||Sure, I had that error before contacting MS Support for the fix.

In my case, it was because the .NET Programmability Support for Excel 2007 was not installed. The Add-ins are written in .NET, so no support means no add-ins.

1. Under Add/Remove Programs, select Office 2007.
2. Select Change.
3. In the pop-up, select Add or Remove Features.
4. Under Microsoft Excel, check to see if .NET Programmability Support is Not Available, and change it to Run from My Computer. (I think this is not installed by default, not sure)

That fixed it for me. If not, check if .NET Framework 2.0 is installed as well. It really seems to me that it's a .NET problem.

Good luck!

-Tim

|||

Tim,

This is freaky. I discovered this about an hour before I received your response.

The .Net stuff is definitely not installed by default in the Excel 2007 typical installation.

Thanks for the tip this solved my problem for the data mining ribbon.

However, I still cannot get the table add-on package to install the table ribbon.

-Brian