- 500 GB DW - 5 GB in smaller DBs - 220 GB TempDB - 350 GB in Log files.
My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.
I have several IO locations:
- 540 GB in-server HDD 15k RAID10 - 1 TB HDD 15k RAID10 on SAN (separete controller) - 2 TB HDD 15k RAID10 on SAN (same controlller as below) - 800GB SSD RAID10 on SAN (same controller as above)
Data warehouse has 2 fact tables that are absolutely crucial and quite large.
Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:
- FILEGROUP1: for 1st crucial Fact Table - FILEGROUP2: for 2nd crucial Fact Table - FILEGROUP3: for tempDB - FILEGROUP4: for dimensions data - FILEGROUP5: for the rest of facts data - FILEGROUP6: for dimensions indexes - FILEGROUP7: for the rest of facts indexes - FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large) - FILEGROUP9: for the rest of log files - FILEGROUP10: others
How should I organize them across available drives? I was thinking about sth like:
I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.
Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.
The following SQL works on Access and Oracle to return the number of Groups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is the equivalent in MS-SQL?
Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion )
Counting groups I have the following code that is grouped by name
Mike G ( from 2000 €“ 2003) Address Raise Test 1 Test 1
Mike G ( from 2004 €“ 2007) Address Raise Test 2 1 Test 2 1
Mark G ( from 2004 €“ 2007)
Address Raise Test 2 1 Test 2 1
Total 6 (I get this without any problem)
The problem I am having is trying to find out how many groups we have to use the value for some other calculation. So I did this Count(Field .Name) wich will give me 6 instead of 3 . The reason is in my dataset it will be something like that
Name add raise Period Mike G Test 1 2000-2003 Mike G Test 1 2000-2003 Mike G Test 1 2004-2004 Mike G Test 1 2004-2007 Mike G Test2 1 2004-2007 Mike G Test2 1 2004-2007
If I do CountDistinct(Field .Name) it will give me 2 which is also wrong . So how can I count many groups we have. I spend a lot of time on this please help!
I have a table which has 2 columns and the data is like below
API_Number       Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A 1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group B 4567Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group C 7896Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group D 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group E
 I wanted to find the API numbers which are repeating in different groups. In the output I want
 API_Number          Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group B 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group E
IF OBJECT_ID('tempdb..#Test') IS NOt NULL DROP TABLe #Test --===== Create the test table with create table #Test([Year] float, Age Int, ) INSERT INTO #Test ([Year], Age)
[Code]...
I queried below to get additional column
Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table tbl_1 (txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive, finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596 jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1 ID2 DisplayData
1 1 A
1 2 B
1 3 C
2 1 A
2 2 B
2 3 C
Parent group is on ID1, child group is on ID2, report would show:
1 1 A
2 A
3 A
2 1 A
2 A
3 A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
I have created a local user on Report Server Computer and the user has the administrative rights. When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ). I get the following error.
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user. -------------------------------------------------------------------------------- SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully. The system is up for a month but this problem occured today?!? What could be the problem?!?
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow. I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID 2 from EvalAnswers 3 where CoID=@CoID 4 and EvaluatorID=@EvaluatorID 5 and (Scr0=0 and Sugg0 is NULL) 6 --group by TeamID 7
Hi, I would like to create groups in SQLServer2000.Do i need to do this by roles or can i create groups for each database.I read some where that there where no groups for SQLServer7.0. Is this option available for the latest versions. Any help will be appreciated.
I have a Attendance table named say Attendance which has Class and Status along with some other fields.Class will have the name of the class the student is in,whereas the status will have the Absent/Present data.There is another table named say ClassMaster which is the master which will have all the classnames and there is a column of ClassTeacher which will have the EmployeeCode.This employeeCode comes from a table EmployeeMaster.My requirement is i want to get the count of Absent students in Attendance table depending on the Class.For Example if there are two class say ClassA and ClassB,then i should get the count of the Students from Attendance table that belong to Class A and Class B.How cn i do this in one Query.
I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's?
Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so?
My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides.
Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column.
How do I put information only at the top of a page and have it change whenever a group occurs.
I want to do something like this. Have this on the top of every page:
Country: current country
State: current state
City: current city
There is a group on country, state and city. Whenever city changes the next page will display a new city. Whenever state changes the next page will display the new state in the header.
I have already put Country, State, and City into their own group headers. However, if either of them change in the middle of the page then the group header repeats. I have group header repeat on so I can see it on each page. If I choose the Page break at start then it breaks but I don't want it to break. I want it to run on the same page if it passes a group and then have the next page display relevant information for the group.
Putting it in the table header works as far as keeping them all on the top of the page but it doesn't change when a grouping ends.
I have my first SSRS report going, after years on Crystal and Access. It has several groups, each nested within the next...I think. I'm having a little trouble with the groups...well, grouping the way I thought I had it set up. The groups come out looking like my primary group is second instead of first, although when I look at the groups in "edit groups" it appears I have them set up correctly. In Crystal, on the preview page you could see the groups idendified so if there was a problem in a report you could tell what group it was coming from. You could also select and change objects in preview, which made it nice to see the impact of changes. I cannot see either of these features in SSRS. I'm hoping that there's a setting I need to turn on, and also hoping that this product has these user friendly features. Any ideas on that?
Also, in Crystal, when you set up a group or a formula, it became available in the field list within the formula builder, so you could use them by picking them instead of typing the whole thing in again, and that's not available either. Or I can't find it!
Since I'm new to SSRS maybe there are features and settings I haven't found yet, but would appreciate any comments from more experienced users about the overall usability of this tool.
