Ok!! Here's the Problem. I am working on a perf stats report. FileType is First 9 chars of the field FileName. I need to compare a similar filename from this month to last month or before ordered by file size.
Maybe this is a very simple query but currrently my mind refuses to work. Seeking F1.
On an installation of sql 6.5, when I go to performance monitor, there are no sql counters. What gives? How can this be fixed,and do I have to reinstall,(wipeout data) to do it?
Is there anyway that you can automate performance monitor from the cmd line. I.e feed it some parameters like server and counters and receive a log file at the end.
I have a SQL2000 db running a vendor custom app on a Win2000 sp3 server. The front-end is run on Citrix to the client. Can't change the app or the somewhat poor infrastructure of client. users starting to complain about slowness ( we can shadow them and this seems the case). On the SQL box all the standard perf counters look good (buffer cache at 100%, queue length always less than 1, memory and disk look good. The server currently has 2 nics doing load balancing. The network counter bytes/sec averages over 100,000. The network counter output queue length shows an average of 4,294,967,251 (That seems high but in reading on another sql perf site this counter doesn't always work). Any other ideas where I might look. Do these numbers look high? Thanks
I have a server that has the following average readings :
No. CPUs = 1 % CPU = 2 SystemCPU Queue Length = 2 to 4 SQL Server:Buffer ManagerBuffer Cache = 99.85% RAM in the box = 1 GB MemoryPages/sec = 1 to 5 SQL memory in use ( using Task Manager ) = 250 MB Max worker threads = 255 Average number of connection = 60
So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.
As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?
I have a 64-bit SQL Server running on 64-bit OS having 12GB of RAM. The Server only hosts our Application database (its a 30 GB database). There are no major process running on it. Despite of this, the Avg Pagessec shows a count of 1500 and the Avg Page Faultsec shows 22000. Is there any specific reason as to why this is happening? The %idle time of the disk is 87%. Also there is no major load on the server. Do let me know if you need any other input.
Are there really any benefit on using Read Uncommitted Isolation Level or having a NOLOCK hints for retrieve queries when the default Isolation level just Read Committed (not using COM+). I'm confused why the Community Server uses this technique perhaps for perf issues but I couldn't see any reason why...
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.
I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.
I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice. I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?
I see a LOT of the following messages in my progress, when running with default settings: [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...
However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...
I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.
I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:
1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.
OR
2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.
The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?
This webcast from our partner, ETI, may be of interest to readers on the forum - we see questions quite often about high-performance interaction with other databases.
Trying to install 2005 Dev edition in xp pro, sp2.
Performance Monitor Counter Check Failed Error. Had to stop the install twice at the point it verified what components were to be included as the docs were not going to be installed. Resolved the issue.
Now it won't pass the system config test because the registry is not the way it wants it. I look up the messages and the only solution is to hack the registry and risk my system.
This is nuts.
Isn't there some way to restart cleanly without hacking registry keys ?
I am trying to come up with a way to save the IP of the last computer that ran an update in our servers. I don't want to have to re-write the .net code, so I am wondering if there is a way, using functions available in SQL 2000 that I can get the IP of the machine running the update. I know that SQL server 'knows' this information because it can log it in it's log files, but I don't know how to get at it from a stored procedure. Thanks.
My mgr wants to know - # of times a particular kind of service (Service ID - AAA) was done on a particular kind of program (Program ID - SP) in 2005 - what is the Service percentage? For example, service was done 40% of the time - Average cost of the service for those programs that had it
I am trying to create a query which should return 4 columnsFacility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicantsI would be glad to even have just 3 columns since, the Total can be computedin the display table (ColdFusion interface)Facility | NoOfActiveApplicants | NoOfArchivedApplicantsSo far I have the following query, which returns just 2 rows with bothNoOfActiveApplicants & NoOfArchivedApplicants under the same column.Am stuck here, any help is apprecialted. Thanks in advance!selectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblapplicant a, tblJobDB b, tblfacilities cwhere a.jobid = b.jobidand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityunion allselectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblArchiveapplicant a, tblArchiveJob b, tblfacilities cwhere a.JobVacancyNumber = b.JobVacancyNumberand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityorder by facility
How can I write a query to output the balances of unpaid invoices into aging columns? I have invoices, lineitems, and payments tables. I have a query that will join the 3 tables to determine which invoices are unpaid. I need to write an aging report in Access 2000 with the unpaid invoices listed with the balance under the proper heading (ie:<30, 30-60, 60+). Any ideas on how to do that? I can write a query to pull one date segment at a time but I can't get them all into one query with different headings to base a report on. My customer had it working in Access 2000 but he upgraded to SQL Server 7 with an Access 2000 Project as a front end and we can't get it to work. It's driving me crazy! TIA!!!
I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.
AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230
Hello all,I need to do the following task repeatedly. I have been doing itmanually until now and I am thinking if there is any way I couldautomate the whole process.Here is the task:For a selected group of tables, I need to create an Excel file with oneworksheet per table. The worksheet contains information about thetable.Here is an Example of Person table with fields PersonId, LastName,FirstName ...Column Name -> PersonId LastName FirstName ...Type -> char char char ...Length -> 6 50 50Data -> P00001 Smith John .... P00002 Smith Joan ...
Hi, it's my first time for using report model as the datasource of reporting service. But I don't know how to build the query string to get data from the report model. Can anyone help me?
What needs to be done to allow a user to input data in a "From query report parameter"?
The "From query" option was used to show all possible selections the user can do but what if the user would now like to select for partial key...i.e.: like '%tss%'
I would like to avoid the Multi-Value parameter option.
NOTE: The report is done through Microsoft Visual Studio / RDL.
I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.
we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.
ABSENCES AWAY = Start of an absence from the building RETURN = End of an absence from the building A person during their reisdency will have multiple Absences An absence could start on the same day a previous absence ends Absences vary in length CS CS0-CS10 (each is a different TYPE) of communicatio
[code]....
Query in DESIGN View
SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM CRM_CSLDB.dbo.vCommunication
[code]....
Results from QUERY in DESIGN VIEW
TYPE DATECOMPIDCOMMIDSEQ AWAY2011-02-24 00:00:00.00051747531 RETURN2011-03-31 00:00:00.00051747542 AWAY2011-03-28 00:00:00.00064740681 RETURN2011-04-30 00:00:00.00064752972
SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate, dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM dbo.vCommunication INNER JOIN
i have a problem with a report. if i try it with vs2005 and the preview it works. if i call the report from my web-application i get no error-message, but i only get my headlines and no query-results.
i use reportingservices 2005, vs2005 and .net 1.1 my datasource is an olap-cube.
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.
I am newbie and i am working on SSRS to create some reports.
I have a reprot where i am displaying ProviderNos in the first column and based on provider i need to get some other information from another database and display it in the same line with Provider.
Below is my report format.
ProviderNo Speciality 1111111 A 2222222 B 3333333 C
The problem is, PrivderNos are from different database so i am getting it in separate Dataset. And Speciality is in Different Database so i am getting it in separate dataset. But i want both of them to be displayed in same table on report.
Below is the query for ProviderNos: Select Prv_No from server1.database1.dbo.Providers where MemberNo='xxxxxx'
Below is the quer for getting Speciality: Select Speciality from server2.database2.dbo.Prv_SP where prv_no IN (@prvno)
If i am assigning SpecialityDS to the table, it displays correct values for each n every provider. But i dont know how do i make it in such a way that it displays the result as above format.
Is there a way to create an expression that references the DataSets / Queries? I'd like to be able to create an expandable text box that shows the raw sql query used to generate the report.
I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:
Mgr ID Manager Name
1 John Q Manager
2 Jane Q Manager
3 Jack O Lantern
I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.
Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?
